CODEWITHSUNDEEP
excelvbacombobox
franciscofcosta
franciscofcosta

Reputation: 843

Select ComboBox AfterUpdate Excel VBA

I am writing a script for a UserForm through which users can register to gain access to a Database. The UserForm has three fields, Username, Password and Confirm Password.

I have made it so that, after the user chooses a username, the script runs a VLookUp through the existing usernames to check if the username chosen already exists. If so, a MsgBox pops up, advising the selection of another username. In this case, all three fields of the UserForm are cleared. I would like to make the cursor be positioned in the Username field so that the user can straight away fill in a different username. However, after all fields are cleared, the password filed is the one selected instead. How can I solve this? Thank you for your help.

This is the code I have written:

Private Sub usernameinput_AfterUpdate()

Dim username As String
username = usernameinput.Text

Dim temp As String
On Error Resume Next
temp = WorksheetFunction.VLookup(Me.usernameinput.Value, Range("Usernames"), 1, 0)

If username = temp Then
    MsgBox ("The username chosen already exists. Please chose a different username."), vbOKOnly + vbInformation, "Existing Username"
    Err.Clear
    temp = ""
    Me.usernameinput.Value = ""
    Me.passwordinput.Value = ""
    Me.passwordconfirm.Value = ""
    Me.usernameinput.SetFocus
    On Error GoTo 0
End If

End Sub

Upvotes: 0

Views: 2546

Answers (1)

user3598756
user3598756

Reputation: 29421

you could act like follows:

in your UserForm code pane:

  • declare a userform scoped variable

    Dim reset As Boolean

  • insert this Sub

    Private Sub HandleReset()
    If reset Then
        Me.usernameinput.SetFocus
        reset = False
    End If
End Sub

  • add all other UserForm controls Enter event handler to call HandleReset() like follows:

    Private Sub passwordconfirm_Enter()
    HandleReset
End Sub

Private Sub passwordinput_Enter()
    HandleReset
End Sub

Upvotes: 1

Related Questions