problem changing password

[davegb]

I'm writing a macro to change the passwords on some of the spreadsheets
in a workbook. For now, the ones with "Monthly" in the sheet name. The
code brings up a userform which asks for the old password, then for the
new password to be entered twice. The program compares the 2 entries to
verify, then is supposed to change all the passwords for the "monthly"
sheets. It works fine finding only the "monthly" sheets, then hangs on
the first sheet with the old password line highlighted and an error
message that the old password is incorrect. When I put the cursor over
the old password, variable sPWORD1, the correct old password is shown.

For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "*Monthly" Then
ActiveSheet.Unprotect Password:=sPWORD1 <---- Password not
correct
ActiveSheet.Protect Password:=sPWORD2
End If

Next

Any ideas on what I'm missing?

Thanks.

 

[alan_stew]

Dave...In your code you are looping through the worksheets, but
'ActiveSheet' keeps referring to whatever sheet is active when the code
starts running, unless you change that.
The easiest fix would seem to me to be to change "ActiveSheet" in the
code below to "ws".
My guess is that's all you need to do the trick.
Alan

 

[davegb]

Dave...In your code you are looping through the worksheets, but
'ActiveSheet' keeps referring to whatever sheet is active when the code
starts running, unless you change that.
The easiest fix would seem to me to be to change "ActiveSheet" in the
code below to "ws".
My guess is that's all you need to do the trick.
Alan

Doh! Thanks, Alan, that did the trick!