Password protect worksheet.

[azland00123]

I am trying to find a way to have this macro run each time the work book is opened. At this point once the password is entered the sheet is unprotected until someone reruns the macro.

What would be best is if this work sheet was protected in such a way, that if someone clicked on the tab to open the sheet they were prompted for a password. once the password is entered they have full editing capabilities. Then when the work book is closed and opened again the password restriction would be reset. I don't know if this is possible but it sure would be nice.

Please Login or Register  to view this content.

[Palmetto]

How to run macros on a protected sheet

[azland00123]

Palmetto,
Thanks, That worked perfect for getting the worksheet to be protected upon start up.
Now If I could just get the password screen to pop up when ever that sheet is selected life would be grand.
Basically
If sheet1 is active then enter password
if the password is false then protect the sheet
if the password is true then unprotect the sheet

Please Login or Register  to view this content.

This is what I ended up with only in a different location then the other and I took out all the other stuff I did not need for this application. I am surprised at how close I was on my last attempt. This must mean I am learning something.

Anyone want to take a crack at the second half of this? You would be my friend forever!

[john55]

maybe this

Please Login or Register  to view this content.

[Palmetto]

Now If I could just get the password screen to pop up when ever that sheet is selected life would be grand.

Using the worksheet_activate event

Please Login or Register  to view this content.

Caution: you should probably use the worksheet deactivate event to automatically turn on sheet protection when leaving the sheet.

[azland00123]

Ok I am so close. I have no idea why this wont work. In this version of the code the password box will not come up at all.

Please Login or Register  to view this content.

If I remove the if statement it works ok.

Please Login or Register  to view this content.

The only problem here is that once the password has been entered every time that sheet it brought up it brings up the dialog box again. This time it wants to protect the document. I only want it to bring up the password box if the document is protected.
Thanks

[john55]

or

Please Login or Register  to view this content.

[azland00123]

Amazing John,
This is perfect.
Thanks Bob

[john55]

hi,
Thank you and glad it helped you! The rep goes to Palmetto!