protect / unprotect multiple sheets

[Guest]

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.

 

[Gord Dibben]

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

 

[Guest]

Wow - thank you
----- Gord Dibben wrote: ----

Lis

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

Sub ProtectAllSheets(
Application.ScreenUpdating = Fals
Dim n As Singl
For n = 1 To Sheets.Coun
Sheets(n).Protect Password:="justme
Next
Application.ScreenUpdating = Tru
End Su

Sub UnprotectAllSheets(
Application.ScreenUpdating = Fals
Dim n As Singl
For n = 1 To Sheets.Coun
Sheets(n).Unprotect Password:="justme
Next
Application.ScreenUpdating = Tru
End Su

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

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

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

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

Gord Dibben Excel MV


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

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Thanks - I appreciate it