MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

Posted by Dave Hawley on April 17, 2001 3:39 PM

Hi Robert

Place the password within the code like this:

Dim Shts as Worksheet

Sub UnProtectAllSheets()
For Each Shts In ThisWorkbook.Worksheets
Shts.Unprotect password:="secret"

Sub ProtectAllSheets()
For Each Shts In ThisWorkbook.Worksheets
Shts.Protect password:="secret"

Don't forget to lock the VBE project!


OzGrid Business Applications