Macros for Protect/Unprotect all sheets in a workbook

[Paul Sheppard]

I have been using the macros below for Protecting/Unprotecting all
sheets in a workbook, they work ok unless the sheets are password
protected, in which case the Unprotect drop down appears and I have to
enter the password for each sheet

Is there any way to change the macros so that irrespective of how many
sheets there are I only have to enter the password once to either
unprotect them all or protect them all

If possible the macro should still run for sheets with no password

Sub unprotect_all()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect
Next
End Sub

Sub protect_all()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Protect
Next
End Sub

 

[Norman Jones]

Hi Paul,

Try something like:

'======================
Sub unprotect_all()
Dim wks As Worksheet
Static PWORD As String


PWORD = InputBox("Please Enter Password")

On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect Password:=PWORD
Next
End Sub
'======================

'======================
Sub protect_all()
Dim wks As Worksheet
Static PWORD As String

PWORD = InputBox("Please Enter Password")

On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Protect Password:=PWORD
Next
End Sub
'======================

---
Regards,
Norman



"Paul Sheppard" <[email protected]>
wrote in message
news[email protected]...

 

[Paul Sheppard]

Thanks Norma

 

[ph8]

I am sorry to Bump an old post, but I just have a question. I wa
looking for an answer and did a search, and found my answer in thi
thread. Thats how I got here. Anyways...

In the code above, there is the line:
"On Error Resume Next"

I understand the rest of the code, and what the subs do and whatnot
but I am a little confused as to the purpose of this line. I woul
just like an explination if anyone can provide one. Thank you

 

[Dnereb]

on resume next is used to ignore or handle errors yourself

if an error occurs the code will continue on the next line

in this case the error raised if the password is incorrect is ignored
and another try is made.
the real bad part of this code is the lack of the statement:

Code:
--------------------
On error goto 0
--------------------

just before the end of the sub. in this piece of $#%^$%^ you will end
up ignoring errors in the rest of your code resulting in very
unpredictable results.
So take it from me for every _resume_next___ You should have at least 1
_goto_0_