Password reused

[Dean]

I use the same password to protect all worksheets. I want to be able to protect all sheets (or unprotect all sheets) with the password, via a macro. But I don't want someone to be able to find the password in the text of a macro. So, I guess a compromise is to have it ask me for the password once, but then reuse that answer for all the other worksheets it then protects (or unprotects). Can someone give me some code that would unprotect sheets 1 through 3 with the password "joe", in this manner, i.e., entered by the user only once?

Thanks!
Dean

 

[Dave Peterson]

You can protect the code in your project.

Inside the VBE
tools|VBAProject Properties|Protection tab
Give it a nice, memorable password and check the lock project for viewing box.

Save and close your workbook and you'll see that the code can't be seen by the
average user.

Be aware that there are ways to break this protection--as well as ways to just
unprotect worksheets. So don't assume your data is safe from prying eyes.

If you really wanted a macro that prompted you for a single password:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim myPWD As String
Dim iCtr As Long

myPWD = InputBox(Prompt:="Enter the common password")

If myPWD = "" Then
Exit Sub
End If

For iCtr = 1 To 3 'worksheets.count
Set wks = Worksheets(iCtr)
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect Password:=myPWD
If Err.Number <> 0 Then
MsgBox "not the correct password for: " & .Name
Err.Clear
End If
End If
End With
Next iCtr

End Sub

ps. This is a text only newsgroup. Please post in plain text--not HTML.

 

[Dean]

Yes, that PW protection of macros does the trick. I should always use this
since a user inadvertently editing a macro can be a disaster. But I have a
question: If the macro crashes, can they still go into debug and tell me
where it crashed? Or do they lose that visibility? If so, I guess they can
just e-mail me the file, but they prefer an instantaneous fix!

Thanks so much, Dave.

Dean

 

[Dave Peterson]

If the project is protected from viewing, then that includes after crashes, too.