CODEWITHSUNDEEP
vbaexcel
John
John

Reputation: 183

set and checking password of a workbook

I want to create and protect a workbook.

This is what I did for protection:

Sub Protege()
    Dim f As Worksheet
    Dim mdp As String

    mdp = InputBox("mdp est ?")

    For Each f In ActiveWorkbook.Worksheets
        f.Protect mdp, True, True, True
    Next f
End Sub

Now, this is what I did to unprotect this workbook:

Sub unprotected()
    Dim f As Worksheet

    For Each f In ActiveWorkbook.Worksheets
        f.Unprotect
    Next f
End Sub

but I want to check the password first. I mean entering a password and see if it matches with the true password.

Upvotes: 1

Views: 1040

Answers (2)

Ralph
Ralph

Reputation: 9434

If I understand correctly, then you can verify the password simply by trying it out and catching any possible error while doing so:

Public Sub ProectSheet()

For Each ws In ThisWorkbook.Worksheets
    ws.Protect "pw"
Next ws

End Sub

Public Sub UnprotectSheets()

strPW = InputBox("PW?")
For Each ws In ThisWorkbook.Worksheets
    On Error GoTo WrongPassword
    ws.Unprotect strPW
Next ws
MsgBox "All sheets are now unprotected."

Exit Sub

WrongPassword:
    MsgBox "The password you entered is incorrect."
    Exit Sub

End Sub

Upvotes: 1

tyg
tyg

Reputation: 14813

There is no way to test for the sheet password except trying to unprotect the sheet with that password.

Just do f.Unprotect mdp, although if mdp contains an invalid password you get a runtime error that you probably should catch:

For Each f In ActiveWorkbook.Worksheets
    On Error Resume Next
        f.Unprotect mdp
        lngError = Err
    On Error GoTo 0
    If lngError <> 0 Then MsgBox "Mauvais mdp !"
Next f

If you only want to test if the provided password was correct but want the sheet still protected you can just protect it again afterwards:

For Each f In ActiveWorkbook.Worksheets
    On Error Resume Next
        f.Unprotect mdp
        lngError = Err
    On Error GoTo 0
    If lngError = 0 Then
        f.Protect mdp, True, True, True
    Else
        MsgBox "Mauvais mdp !"
    End If
Next f

Keep in mind though that - as Mat's Mug mentioned in the comments - there is an infinity of matching passwords, because you just have to create one that has the same hash as the original one. And that's pretty easy.

You should never expect a sheet protection to hold against someone who does not know the password. It can easily be circumvented.

Upvotes: 1

Related Questions