Create Master Template that uploads

Hello all,

I'm hoping some of you may help me out creating a master template that uses vba to auto generate data that's inputted into a master list file.

The master template will be updated by multiple users on a weekly basis, thus requiring some restrictions before they can click on a complete button that will upload their data into the master list file.

Restrictions on master template file:
user must fill in Store # section
user must fill in Date section
user cannot use text in the Rates sections: no $0, no "N/A". Either blank or a dollar figure higher than $20.
when user is finished and "Complete & Upload" is clicked, a message pops up asking if they are sure they are complete. (Yes/No box)

When completed, the data is put in the master list file accordingly. (I'm currently transposing the Rates manually).
I would like the data to be placed under the old data as it uploads.
It would be nice to see the Date of when the user Completed & Uploaded the data but also the Date the user inputs into the Date section.

I hope to upload the master template to our sharepoint/dropbox folder for users to access and upload their data - then it syncs to the master list file also in sharepoint/dropbox (but only I can access).

If someone can help me with this project, it would save me a tremendous amount of time and it would be greatly appreciated.

I have upload what the master list file and master template file look like.

Please let me know if I can clarify anything further.

Quite a few views but no bites. Is this too difficult to do? Or is it unclear?
Hoping someone is up for the challenge - it would be a great reference post too.

Hi there,

Take a look at the attached Template workbook and see if it does what you need as far as data validation is concerned - it uses the following code in a standard VBA CodeModule:

Please Login or Register  to view this content.

and the following code in the VBA CodeModule for the Template worksheet:

Please Login or Register  to view this content.

I haven't got around to looking at what's required for the data upload feature - no guarantees, but I'll see if I can provide you with something for that.


Hope this helps for at least some of your requirements - please let me know how you get on.

Regards,

Greg M

Greg - this looks perfect so far! One thing I notice though is it gives the pop-up message that the Store # requires the user input before Complete & Upload but when you click ok, it gives the are you sure you want to upload message. Thus allowing the user to submit the form without the Store #.

It would be great if you could help me get to the next stage of this project and that's to upload the data into the template. I'm not sure how difficult this is (impossible for me).

Hi again,

Thanks for your feedback.

The latest version (not yet posted) of the Template workbook overcomes the shortcoming you mentioned.

As far as the uploading process is concerned, where are the workbooks stored? Are the various Template workbooks stored in folders which are controlled by the Users, and the Master List workbook stored in a centrally-located folder? Are all workbooks (Templates and Master List) stored in the same folder? Some other arrangement?

Obviously the uploading routine will need to "know" where the Master List workbook is located.

I've made some progress in developing the uploading routine, and will be able to finalise it once I have an answer to the above question.

Regards,

Greg M

Hi Greg,

I'm undecided where the workbooks will be held. I'm hopeful I can post the master workbook for users to fill in on SharePoint. Then when they click on complete and upload, the data can be loaded to the master list file that's held either in Dropbox or SharePoint with me having access to it only.

So for example, the Master Template (where users fill out and upload) will be in Folder A in sharepoint. They can open, input their data, complete & upload - then the data is uploaded to the Master file list and the data is cleared from the Master Template for another user to use.

This might be a tricky thing to achieve - but I think I can set it up where if one user currently has the master template form open, others can't access it until that user is complete.

The master file is located in folder B where only I have access to it and can open it up and see all the uploaded data. Folder B can be on sharepoint or dropbox - whichever makes most sense and/or more convenient.

I hope this is clear - please let me know if I can explain anything else.

Thank you

Hi again,

Ok on that, and thanks for your feedback.

I think I'll be able to provide what you need, but I'll be away from computers etc. until July 15, so I won't be able to do anything until that stage. I'd hope to post something here shortly after I return.

Regards,

Greg M

Thanks Greg - looking forward to it!

Hi again,

Back from holidays - sorry about the delay

On the basis of your previous post, I'd suggest that the Master workbook should be located in a centralised location (which all users can access) and that it should be password-protected. You will have the password so that only you will have full access to that workbook. Users will not have the password, but the individual template workbooks will "know" the password so that data can be uploaded to the Master workbook without users having "real" (i.e. meddlesome) access to it.

Does this approach sound as if it would do what you need?

Regards,

Greg M

This sounds like what I need

Hi again,

See if the attached workbooks do what you need.

The Template workbook contains the following code:

Please Login or Register  to view this content.

The highlighted values should be changed to suit your own requirements.


The Master workbook has been protected using the password d7882 - you (i.e. only you) can use this password to view the contents of the Master workbook.

The Master workbook should be stored in the location specified by the sMASTER__PATH constant.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Will this Work in SharePoint, i allways have problems with Excel and SharePoint

Hi nordicdust,

The short answer is "I don't know"

Sorry, I don't use SharePoint so I have no experience of it.

Regards,

Greg M

Greg - I've look at it briefly this morning and so far it's what I need! I will play around with it some more in the next couple days to make sure it's perfect.
Thank you for your help in this.

Hi again,

Ok, thanks for that.

Please continue to keep me posted.

Regards,

Greg M

Greg - wanted to give you an update. I looked over the two files and they work perfectly. I was able to make small adjustments within the VBA code and everything still works smoothly.

I had some trouble setting it up within Sharepoint - perhaps because of the password protection. However, within DropBox, it's just fine.

Thanks again for your help with this.

Hi again,

Many thanks for taking the time to send me your feedback.

Glad to hear that it's working correctly for you now.

Regards,

Greg M