Rob Mulhern
I need to lock down a worksheet once the end user has clicked a button to finalise the data set.
> That's possible
I then need it to save the excel file and also save a PDF copy of the the current worksheet and two others. I have some code for a bit of it.
can someone help with the rest?
> That's would be possible if can see the code.
I also want to make sure the module is locked down and hopefully then nobody can crack any of the passwords in the workbook and worksheets. Is that possible also?
> That's impossible!
Hint: Seems that You are looking for VBA-solution, then this should has opened in VBA Macros. You can move it there.
Thanks for the quick response. I'm really really stuck.
Please see attached file.
There is a "MPR Master template" worksheet which will be copied into each of the "Months" Worksheets.
Once in those I need the following to work once they click a button within that worksheet for the month they are reporting on.
Code so far:
Module 1:
1. First part is a mandatory field check.
This works but I would like it to display all the mandatory fields in one message box.
The idea is they can't save until all mandatory fields are complete but I don't want to do that one at a time if possible (if they have more than one specified mandatory field missing).
So what I don't want is if they click say, perhaps a "Save" (Finalise) button, it then tells them they have missed one mandatory field. They fill in field and click "Save" again, it then says there is yet another field to fill out.... (would get annoying so best to display all in one message box up front).
2. Lock the worksheet - works after mandatory fields are complete. Ideally I would like to save the workbook by Current Workbook Name, Current Worksheet Name and Current Year variables.
Please see notes I added in the module. I need help in coding that.
3. Then save as PDF.
It has evolved now that 3 PDF's need to be saved with similar naming convention to the above for Worksheets - "Dashboard", the current months worksheet so "January" or "February" (what ever one they are using the button on to "Save") and "Photos".
Module 2:
I was trying to get all the "Month" worksheets to rename based on the end users start month of a project which they would select.
So The end user would have a "drop down" that they selected from a start month and then the macro would loop through and re-name the worksheets currently named in the master template January to December (12 months).
So If you choose February in the "drop down" menu, "January 19 is re-named to February 19 and subsequently each worksheet moves on a month through to 12 months later (or 24, 36 etc).
So if September 19 is chosen as the first month (Start month of the project) January to December becomes September 19 to August 20.
Module "SaveAsPDF":
How can I this link to Module 1? So I want the end user to have the naming convention already used for the file name but also choose where to save it?
Thank you so so much!