CODEWITHSUNDEEP
excelvbacomboboxuserform
Anca Vulc
Anca Vulc

Reputation: 145

Mismatch error in completing combo box VBA

I have a problem when trying to type in my combo box (in a userform) in order to find a match. When I type a wrong letter/number it immediately gives a Mismatch Error and directs me to the VBA code. How can I avoid that? Is there something I can add to my code or to change in the properties? Because for the user it is common to type something wrong and I don't want to redirect the users to the code.

This is the code for my combo box:

Private Sub ComboBox3_Change()

If Me.ComboBox3.Value <> "" Then
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("11")
    Set ph = ThisWorkbook.Sheets("22")
Dim i As String

i = Application.Match((Me.ComboBox3.Value), sh.Range("A:A"), 0)

    Me.TextBox8.Value = ph.Range("D" & i).Value
    Me.TextBox13.Value = ph.Range("P" & i).Value
    Me.TextBox41.Value = ph.Range("B" & i).Value

End If
End Sub

Private Sub UserForm_Activate()
Dim i As Integer

Me.ComboBox3.Clear
Me.ComboBox3.AddItem ""

For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row

    Me.ComboBox3.AddItem sh.Range("A" & i).Value

Next i

Upvotes: 0

Views: 451

Answers (1)

LOKE2707
LOKE2707

Reputation: 312

You need to use error handling statement to skip the part that is generating the error.

Private Sub ComboBox3_Change()

If Me.ComboBox3.Value <> "" Then
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("11")
    Set ph = ThisWorkbook.Sheets("22")
Dim i As String
    or error go to MyHandler
    i = Application.Match((Me.ComboBox3.Value), sh.Range("A:A"), 0)

    Me.TextBox8.Value = ph.Range("D" & i).Value
    Me.TextBox13.Value = ph.Range("P" & i).Value
    Me.TextBox41.Value = ph.Range("B" & i).Value

End If
MyHandler:
' Expected behavior on error
End Sub

Upvotes: 1

Related Questions