Can I made changes when sheets are protected?

[Jeanne724]

I am the main person who creates a excel file for co-workers. I have just
learned by this discussion group yesterday on how to protect certian things
in excel with passwords, and etc.

I go into my excel file daily and make changes. I would like to know is
there a way I can go into my file and make my changes without having to put
in my password for everysheet? Like give just me permission to do everything
with no problem?

 

[Dave Peterson]

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 these lines:

pwd = InputBox(Prompt:="What's the password, Kenny?")
to
pwd = "TopSecretPaSsWord1234_x"