• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Protection

Rob Mulhern

New Member
Hi All,

I need to lock down a worksheet once the end user has clicked a button to finalise the data set.

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?

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?

Thanks,

Roib
 
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.
 
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! :)
 

Attachments

  • New MPR Form Master Blank Template Test.xlsm
    121.8 KB · Views: 3
Rob Mulhern
Very short checking now ...
'0') You should read all lines - especially the last row.

1/2) Why need to have 13 times same layout 'MPR Master Template' and months?

1) one message box -- there are limits how much text can show in one message box
> somewhere eg red cells until needed cells are okay
> Save is 'save' / 'Finalise' should do something else

2) Lock the worksheet -- If something 'lock' then do You need after that ... edit ... something?
If 'yes' then why lock - if 'no' then eg PDF?

3) SaveAsPDF -- So?

Modules --- before those ... need to know what do You need?

As I wrote: Very short checking now ...
 
Back
Top