Macros for Protect/Unprotect all sheets in a workbook

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

One way:

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect vPword
Next
End Sub

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Note: Unprotect_All will allow you to unprotect all sheets, even if
there are different (or no) passwords on each sheet.

In article <[email protected]>,
Paul Sheppard
<[email protected]> wrote:

> 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

Thanks, thats really useful