How do I protect all worksheets at once?

  • Thread starter Thread starter Belch
  • Start date Start date
B

Belch

How do I protect all worksheets at once without having to do Tools,
Protection, ProtectWorksheet for every sheet?

I have Excel 97 SR-1.

Belch
 
hi
you could use VBA for this:
sub foo()
dim wks as worksheet
for each wks in worksheets
wks.protect password:="your pasword"
next
end sub
 
Hi Belch!

You'll need VBA for this. Here's some code to protect all sheets with
a password:

Sub ProtectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Protect Password:="not4u2see"
Next n
End Sub

And to unprotect all sheets:

Sub UnprotectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Unprotect Password:="not4u2see"
Next n
End Sub

Or if you'd like fries with that, Sir, try:

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