How do I protect all worksheets at once?

[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

 

[Frank Kabel]

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

 

[Norman Harker]

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.

 

[Dave Hawley]

Hi Belch

This page has full details and intructions for a good way;
http://www.ozgrid.com/VBA/excel-worksheet-protect-all.htm

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****