CODEWITHSUNDEEP
excelvbaif-statementuserform
rnorkett
rnorkett

Reputation: 3

How to require password to terminate a userform in VBA

I want to prevent the user from closing the form and editing the spreadsheet if they don't know the password. I can get the Userform to stay open but I cannot figure out how to initialize the form so all of the command buttons still function.

I have tried using an if statement as described below. The userform stays open but isn't initialized so the user is unable to enter a password or run the userform at all.

Private Sub UserForm_Terminate()

    Password = InputBox("Enter Password")
    If Password = "syntax" Then
        UserForm1.Hide
    ElseIf Password <> "syntax" Then
        UserForm1.Show
    End If
    End

End Sub

There aren't any error messages, but if the VBA editor window isn't open the user has to close Excel completely. Any advice on what to try next is appreciated. Thanks in advance

Upvotes: 0

Views: 818

Answers (1)

Josh Eller
Josh Eller

Reputation: 2065

You can use UserForm_QueryClose to intercept all 'close' actions on a userform. This code would go in the userform itself.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Dim password As String

    password = InputBox("Enter Password")
    If password = "syntax" Then
        Cancel = False 'If password is correct, allow close event to proceed
    ElseIf password <> "syntax" Then
        Cancel = True 'If password is incorrect, cancel the close event
    End If
End Sub

Like K.Dᴀᴠɪs pointed out, this still won't prevent anyone from pausing the code execution and manually closing the form (Excel just doesn't provide that level of security).

Upvotes: 1

Related Questions