Macro to Unlock All Protected Worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Storm - 17 Sep 2007 16:38 GMT

Good morning. A couple of weeks ago, someone from this forum, was very
helpful in my question on how to unprotect protected worksheets, with similar
passwords, with a macro. Below is a copy of the VB script I was given. It
worked but then I realized, the password "hi" is now visible under the macro
script. Is there a way to rewrite the code so that it will not show the
password and instead when the macro is run, one has to enter the password
before the macro can unprotect the protected worksheets?

thank you very much,
Storm

Option Explicit
sub testme()
dim myPWD as string
dim wks as worksheet
myPWD = "hi"
for each wks in activeworkbook.worksheets
wks.unprotect password:=mypwd
next wks
end sub

Reply to this Message

Chip Pearson - 17 Sep 2007 16:49 GMT

Assuming that all the worksheets have the same password, you can use code
like

Sub UnlockWorksheets()
Dim PW As String
Dim WS As Worksheet

On Error GoTo ErrHandler:
PW = Application.InputBox(prompt:="Enter Password", Type:=2)
If StrPtr(PW) = 0 Then
' user cancelled
Exit Sub
End If

For Each WS In Worksheets
WS.Unprotect Password:=PW
Next WS
Exit Sub
ErrHandler:
MsgBox "Invalid Password", vbOKOnly

End Sub

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> Good morning. A couple of weeks ago, someone from this forum, was very
> helpful in my question on how to unprotect protected worksheets, with
[quoted text clipped - 19 lines]
> next wks
> end sub

Reply to this Message

ssGuru - 17 Sep 2007 17:52 GMT

> Assuming that all the worksheets have the same password, you can use code
> like
[quoted text clipped - 51 lines]
>
> - Show quoted text -

And If we wanted to "re-lock" using the same password what would we
add?
Dennis

Reply to this Message