Macros for Protect/Unprotect all sheets in a workbook

[Paul Sheppard]

Macros for Protect/Unprotect all sheets in a workbook

--------------------------------------------------------------------------------

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

Is there any way to change the macros so that irrespective of how man
sheets there are I only have to enter the password once to eithe
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 Su

 

[JE McGimpsey]

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.

Paul Sheppard

 

[Paul Sheppard]

Thanks, thats really useful