You may be able to use a macro.
Select the sheets first (click on the first and ctrl-click on subsequent). But
remember to ungroup those sheets after the macro runs. Otherwise, you'll be
making the same change on each of the selected sheets. This can be very useful
for headers/descriptions. Not so much for real data.
Then use a macro and if you use the same password for all the sheets, it would
look something like:
Option Explicit
Sub UnprotectAll()
Dim wks As Worksheet
Dim pwd As String
pwd = InputBox(Prompt:="What's the password, Kenny?")
If Trim(pwd) = "" Then
Exit Sub
End If
For Each wks In ActiveWindow.SelectedSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect Password:=pwd
If Err.Number <> 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub
Sub ProtectAll()
Dim wks As Worksheet
Dim pwd As String
pwd = InputBox(Prompt:="What's the password, Kenny?")
If Trim(pwd) = "" Then
Exit Sub
End If
For Each wks In ActiveWindow.SelectedSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect Password:=pwd
If Err.Number <> 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub
Then back to excel and save this workbook with a nice name.
Anytime you want to unprotect or protect all the worksheets in any workbook, you
can open this file.
Then activate the workbook that you want to make changes to.
Hit alt-f8
Select the macro
and click Run
If you really wanted, you could embed the password directly in the code (both
procedures) and not be bothered with a prompt.
Change this line:
pwd = InputBox(Prompt:="What's the password, Kenny?")
to
pwd = "TopSecretPaSsWord1234_x"
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
