Macro Help

[John]

I asked a question yesterday about creating a macro that within it, removes
the protection from the worksheet, does what it is supposed to do, and then
puts the password back on. Someone reasponded with that is below, but
unfortunately that did not work.

I have two worksheets, so I don't know if that is causing the problem. The
macro is supposed to change the format of one cell in one worksheet, then go
to the other worksheet2, change that format to the same thing, then go back
to the original worksheet. Is this possible?

 

[SongBear]

I just tried it in an empty spread sheet and it did work. Of course, it will
only work if your password is "password", otherwise you need to substitute
the actual password between the quotes.
What I used to protect and then un protect:

Sub ProtectSheet()
PWD = "password"
Worksheets("sheet1").Protect Password:=PWD
End Sub

Sub UnProtectSheet()
PWD = "password"
Worksheets("sheet1").Unprotect Password:=PWD
End Sub

I put this code in a module, not in the worksheet code page, but I have made
the same macro work in the sheet code page in the past.
I set it up like that so that I could call it from inside another (main)
sub. That way, when I was ready to modify the worksheet in the main sub, i
called the unprotect sub (with the correct password) and then modified the
worksheet, then called the protect sub to secure the worksheet again.
Bear

 

[Zac]

I'm attempting a similar thing as John. All I want is when a user accesses
the spreadsheet and goes to his/her appropriate worksheet, the user can
remove protection then reapply protection using the same password once
they're done. Going to Tools/Protect Worksheet again means having to re-enter
another password. I want a macro that can reapply it easily. The UnProtect
portion of the macro mentioned in this post works fine, but the Protect sub
needs to prompt the user to enter a password, which I could not get it to do.
They simply protect/unprotect with no password involvement.