Verifying that a set password on a worksheet is correct though VBA

Question

I password protected a worksheet and I would like to verify on open that the password hasn't been changed by a user later.

I have tried setting the password again via VBA on a sheet that the password was changed to see if it would error but it did not provide an error.

The following code is what I am currently using to test this and it works but I don't think you should have to Unprotect a sheet then Protect it to verify the password.

Sub CheckSheetPassword()
    Dim ws As Worksheet

    On Error Resume Next
    For Each ws In Worksheets
        ws.Unprotect Password:="Password"
        ws.Protect Password:="Password"
        If Err <> 0 Then
            MsgBox "Failed To Verify Password on: " & ws.Name
            Err = 0
        End If
    Next ws

End Sub

Explanation of my code:

The above code loops through all the sheets in a workbook and checks to see if the sheet protection password has been changed by unprotecting the current sheet with the original password.

It then protects the sheet the sheet with the original password to prevent it from staying unprotected. (If the current password is different than the original password no error is thrown and nothing happens with this step.)

If the code is unable to unprotect the sheet then it will throw an error and a message box will appear to let me know that the password has been changed on that sheet.

I reset the Error count to zero before the next worksheet is evaluated to determine which sheets still have correct passwords.

What I am asking is can this be done without having to unprotect the sheet?

You said you tried to see if it errors but this didn't work? How did you try this if you unprotected the sheet it should error if the password is not the same. — DragonSamu
no. there isn't any property/method which returns the sheet password so that you can match it with another string. Worksheet.Password, it will be cool but defy the whole concept of password :) — cyboashu
Thanks I appreciate it @cyboashu that is the answer I was looking for I didn't there would be another way to check it. — Matt

Trimax Trimax · Accepted Answer · 2016-08-10T06:24:09

After unprotect the sheet with the password You can verify is the sheet is protected.

Here is the complete code:

Sub CheckSheetPassword()
    Dim ws As Worksheet

    On Error Resume Next
    For Each ws In Worksheets
        ws.Unprotect Password:="Password"
        If ws.ProtectContents = False Then
            ' The sheet is unprotected, so password hasn't changed
        Else
            ' The sheet is protected, so password has changed
            MsgBox "Failed To Verify Password on: " & ws.Name
        End If
    Next ws
End Sub

Verifying that a set password on a worksheet is correct though VBA

2 Answers