Passwords For Dave Hawley
Posted by Robert Hutchison on April 17, 2001 2:39 PM
I have 10 work sheets in a file which are all password protected with the same password. Is there any way to unprotect and password protect all the worksheets at one time. I can not seem to be able to get a macro to do this for me.
On Error GoTo WrongPassword For Each WSht In ActiveWorkbook.Worksheets WSht.Unprotect Password = SPassWord Next WSht SPassWord = "" Set WSht = Nothing Exit Sub WrongPassword: MsgBox "Wrong password", vbCritical Set WSht = Nothing End Sub Sub ProtectAll() 'Macro to Protect ALL worksheets Dim SPassWord1 As String, SPassWord2 As String SPassWord1 = "" SPassWord2 = "" SPassWord1 = InputBox("Password", "Protect all sheets") If SPassWord1 = "" Then Exit Sub SPassWord2 = InputBox("Retype Password", "Protect all sheets") If SPassWord2 = "" Then Exit Sub If SPassWord2 <> SPassWord1 Then MsgBox "Passwords do not match", vbCritical SPassWord1 = "" SPassWord2 = "" Run "ProtectAll" End If On Error Resume Next For Each WSht In ActiveWorkbook.Worksheets WSht.Protect Password = SPassWord1 Next WSht SPassWord1 = "" SPassWord2 = "" Set WSht = Nothing End Sub
In unprotecting the worksheets. I keep getting a wrong password message when I enter the correct password for all the worksheets. With some modification I can get it to work but I have to enter the same password for each worksheet. What I want to do is enter the password once and it will unprotect all the worksheets with that password.
Hopefully you can help