protect / unprotect multiple sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
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.
 
Back
Top