Locking an entire workbook

[Dino]

Is there any way to lock an entire workbook, so that the
individual cells (except the ones that I want to be
excluded) on each sheet cannot be modified? The "protect
sheet" function is too tedious since I have many sheets,
and the "protect workbook" does not seem to protect
individual cells from being modified. Can anyone can help
with this?

Dino

 

[Gord Dibben]

Dino

To protect all sheets at once you can run a macro. Which cells to lock and
unlock on each sheet will be up to you.

If they are the same on each sheet, try grouping the sheets and Formatting
them then ungroup and run the ProtectAllSheets macro.

I also included an UnprotectAllSheets macro.

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

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[Norman Harker]

Hi Dino!

Try copying and pasting the following to your Personal.xls file or to
a module in the workbook that you want to use them in:

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

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


Or, better still, you might use:

Public Sub ToggleProtect1()
' From J E McGimpsey
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
MsgBox Mid(statStr, 2)
End Sub

Assign this macro to a particular key or toolbar button and it allows
you to toggle between protected and unprotected status.

Any further help on how to achieve the above, then post back and
someone, if not me, will walk you through it.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 6th August 2003: Bolivia
(Independence Day), Bulgaria (The Transfiguration), Jamaica
(Independence Day), United Arab Emirates (Accession of H.H. Sheikh
Zayed). Observances: Hiroshima Day (08:15 Local Time 6th August 1945),
Hiroshima Peace Ceremony (Shinto), The Transfiguration (Christianity).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.