Macros for Protect/Unprotect all sheets in a workbook

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

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]...
>
> 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
>
>
> --
> Paul Sheppard
> ------------------------------------------------------------------------
> Paul Sheppard's Profile:
> http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=392821
>

Thanks Norman

I am sorry to Bump an old post, but I just have a question. I was looking for an answer and did a search, and found my answer in this 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 would just like an explination if anyone can provide one. Thank you.

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:

Please Login or Register  to view this content.

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