CODEWITHSUNDEEP
vbaexcel
Matt
Matt

Reputation: 503

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

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?

Upvotes: 3

Views: 1430

Answers (2)

Matt
Matt

Reputation: 503

This question was answered in the comments stating that the way I proposed was correct.

Upvotes: 0

Trimax
Trimax

Reputation: 2473

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

Upvotes: 1

Related Questions