I saved this from a previous post:
Maybe you can create a top secret macro that unprotects all the worksheets in
the activeworkbook and then another macro that protects all those sheets.
Start a new workbook
hit alt-f11 (to get to the VBE where macros live)
Hit F4 (to see the project explorer--kind of like windows explorer)
select your project
Insert|Module
Paste this into the newly opened code window:
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 ActiveWorkbook.Worksheets
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 ActiveWorkbook.Worksheets
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.)
