Multiple sheet unprotection not working

[Bug289]

Hi,
I have trawled through the substantial number of posts in the discussion
group on protecting and unprotecting multiple spreadsheets at the same
time...and been semi-successful.

I am protecting a spreadsheet so that my boss can edit the financially
sensitive areas of the sheets and then protect it so that they can't be
edited/seen by others entering data on the same sheets.

I want to make it as easy as possible for him to protect and unprotect
without having to find the VB editor so I have set up command buttons to run
a protect and unprotect sub. However, I need them to prompt him for a
password.

I can get protect to work without any issues but when I try to get it to
unprotect the code fails, flips to the VB editor and tells me the password is
incorrect even if it isn't.
The versions of code in the discussion group that involve an 'On Error
Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but
can someone tell me what!

 

[OssieMac]

Can you post the code you are using and mark the line that fails

 

[Bug289]

These are the two subs. I took out the password prompt for the 'ProtectAll'
sub to simplify it while I get the unprotect working!

Sub ProtectAll()
Dim WS As Worksheet
'Dim Pword As String
'Pword = Application.InputBox("Enter password to lock spreadsheet",
"Password Check")
For Each WS In ActiveWorkbook.Worksheets
WS.Protect Password:="Fred"
Next WS
ThisWorkbook.Protect Password:="Fred", Structure:=True

End Sub



Sub UnprotectAll()
Dim wks As Worksheet
Pword = InputBox("enter the password")
On Error GoTo endit
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect Password:=Pword
Next wks
ThisWorkbook.Unprotect Password:=Pword
endit:
MsgBox "Incorrect Password"
End Sub

 

[Bug289]

Oops, forgot to mark the failing line:

In this version it goes from input box to 'endit' so there's blatantly an
error. If I take 'On Error' out it comes up with:
'Run-time error 1004. The password you supplied is not correct...'

Sub UnprotectAll()
Dim wks As Worksheet
Pword = InputBox("enter the password")
On Error GoTo endit
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect Password:=Pword
Next wks
ThisWorkbook.Unprotect Password:=Pword
endit:
MsgBox "Incorrect Password"
End Sub

 

[OssieMac]

Untested but try unprotecting in the reverse order of protecting. That is
unprotect the workbook first then the worksheets.

If this does not work, on which line does the code fail?

 

[john]

see if this does what you want:

Sub UnprotectSheets()
Dim SH As Single, pwd

top:
On Error GoTo myerror

pwd = InputBox("Enter password")

If Len(pwd) = 0 Then GoTo ExitSub

Application.ScreenUpdating = False

For SH = 1 To Sheets.Count
Sheets(SH).Unprotect Password:=pwd
Next SH

ThisWorkbook.Unprotect Password:=pwd


myerror:
If Err > 0 Then
MsgBox (Error(Err))
Err.Clear
GoTo top
End If

ExitSub:

Application.ScreenUpdating = True

End Sub

 

[Bug289]

Nope.

It fails on
wks.Unprotect Password:=Pword

It does unprotect the workbook if I put that first

 

[Bug289]

Sorry, no.
Now it just brings up the error in a message box before returning to the
top, I type the password again and I get the same runtime error. Same line
is failing. There's something wrong with the '.Unprotect Password:=pwd' line.

 

[john]

worked ok for me
error suggests that all your sheets may not be using same password?
Try unprotecting manually & then reapply your password to all sheets to
ensure all same.

 

[Bug289]

Doh! It was a hidden spreadsheet that still had some other password on it!

Thanks, you're a star!