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


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392836

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 ,
Paul Sheppard
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


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392836