protect / unprotect multiple sheets

Hi, I'm trying to protect and of course, unprotect multiple sheets at the same time, without using the Tools/Protection/Protect Workbook option - that option is not protecting the way I want. I have many sheets in my file and would like to be able to select a range of them (I do know how to do that) and then do Tools/Protection/Protect Sheet and have the whole range of selected sheets be protected with the same password that I enter only once. Does anyone know how to do this? I'd really appreciate it if you can help. Thanks.

Lisa

Cannot be done by grouping sheets. VBA required. The code below will
protect/unprotect all sheets. To protect/unprotect just certain sheet use the
code below the ***************** line.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

************************************************** ********

Sub Protect_SomeSheets()
Dim sht As Worksheet
For Each sht In Sheets(Array("Sheet1", "Sheet2", "Sheet9", "Sheet11"))
sht.Protect Password:="justme"
Next sht
End Sub

Sub UnProtect_SomeSheets()
Dim sht As Worksheet
For Each sht In Sheets(Array("Sheet1", "Sheet2", "Sheet9", "Sheet11"))
sht.UnProtect Password:="justme"
Next sht
End Sub

You will have to edit the Sheets(Array names to suit.

Gord Dibben Excel MVP


On Fri, 30 Jan 2004 16:36:09 -0800, "Lisa Williams"
wrote:

Hi, I'm trying to protect and of course, unprotect multiple sheets at the same time, without using the Tools/Protection/Protect Workbook option - that option is not protecting the way I want. I have many sheets in my file and would like to be able to select a range of them (I do know how to do that) and then do Tools/Protection/Protect Sheet and have the whole range of selected sheets be protected with the same password that I enter only once. Does anyone know how to do this? I'd really appreciate it if you can help. Thanks.

Hi Lisa!

And to complete Gord's collection we have:

Public Sub ToggleProtect1()
' From J E McGimpsey. Modified by NH
Application.ScreenUpdating = False
Const PWORD As String = "not4u2see"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
Application.ScreenUpdating = True
MsgBox Mid(statStr, 2)
End Sub

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Thanks - I appreciate it