Combobox Type Mismatch is driving me insane

[TheDude0901]

Greetings,

I'm trying to create a very simple Userform with a single combobox named "combobox1" to select a product part number and fill in three textboxes with related data. The problem that I'm having is when I select the first part number that is a string my code works perfectly. If I select any of the numeric part numbers I get a "Type Mismatch" error at the line indicated below.

It needs to work with both integers and strings as our part numbers are all over the board. Some may be "1234567" or "123-45-67" or even "AXL491-48115"

Yes, I'm a VBA newbie and I'm completely at my wits end. Any help would be very greatly appreciated!

Best regards,
The Dude

Private Sub UserForm_Initialize()
    Populate_Combobox1
    ComboBox1.SetFocus
End Sub

Private Sub ComboBox1_Click()
     Show_Paint_Data (ComboBox1.Value)
End Sub

Private Sub Populate_Combobox1()
    Dim v, e
    With Sheets("Sheet1").Range("A2:A20")
        v = .Value
    End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
            If Not .exists(e) And e <> "" Then .Add e, Nothing
        Next
        If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
    End With
End Sub

Private Sub Show_Paint_Data(part_number As String)
    Dim x As Integer
    Dim row_number As Integer
    Dim rng1 As Range
    Dim rng2 As Range
    
    Set rng1 = Application.Worksheets("Sheet1").Range("A2:A20")
    Set rng2 = Application.Worksheets("Sheet1").Range("B2:R20")
    
    row_number = Application.Match(part_number, rng1, 0)                 [SIZE=4][I][B][COLOR=#ff0000] <-------- BLOWS UP HERE[/COLOR][/B][/I][/SIZE]
    x = 1
    
    Do While x < 4
        Controls("Ident_" & x).Value = Application.WorksheetFunction.Index(rng2, row_number, x)
        x = x + 1
    Loop
    
End Sub

Part Number	Field 1	Field 2	Field 3
ZSDF	2	3	4
2	3	3	3
3	1	2	3

<tbody>
</tbody>

 

[mikerickson]

Try dimensioning row_number as Variant. And then testing IsError(row_number) to see if part_number has been found by the Match.

 

[TheDude0901]

Try dimensioning row_number as Variant. And then testing IsError(row_number) to see if part_number has been found by the Match.

I changed it to the following:

Private Sub Show_Paint_Data(part_number As String)
    Dim x As Integer
    Dim row_number As Variant
    Dim rng1 As Range
    Dim rng2 As Range
    
    Set rng1 = Application.Worksheets("Sheet1").Range("A2:A20")
    Set rng2 = Application.Worksheets("Sheet1").Range("B2:R20")
    row_number = Application.Match(part_number, rng1, 0)
    x = 1
    
    If IsError(row_number) Then
        MsgBox "error"
    Else
        MsgBox row_number
    End If
    
    Do While x < 4
        Controls("Ident_" & x).Value = Application.WorksheetFunction.Index(rng2, row_number, x)
        x = x + 1
    Loop
    
End Sub

When I selected the string part number the message box showed the correct row number. Selecting an integer part number the message box showed "error".

 

[TheDude0901]

I tried this as well.

It will pop up a message box with "error" but I assume that's because of the type mismatch error and not being able to match it to the number.

Private Sub Show_Paint_Data(part_number As String)
    Dim x As Integer
    Dim row_number As Variant
    Dim rng1 As Range
    Dim rng2 As Range
    
    Set rng1 = Application.Worksheets("Sheet1").Range("A2:A20")
    Set rng2 = Application.Worksheets("Sheet1").Range("B2:R20")
    
    'row_number = Application.Match(part_number, rng1, 0)
    x = 1
    
    If IsError(Application.Match(part_number, rng1, 0)) Then
        MsgBox "error"
    Else
        MsgBox row_number
    End If
    
    Do While x < 4
        Controls("Ident_" & x).Value = Application.WorksheetFunction.Index(rng2, Application.Match(part_number, rng1, 0), x)
        x = x + 1
    Loop
    
End Sub

 

[Sektor]

May be you should change "Value" property to "List"?

 

[TheDude0901]

No, that breaks everything. I get a "Type Mismatch" on everything I select.

 

[mikerickson]

You could also change the type of part_number to variant

Private Sub Show_Paint_Data(part_number As Variant)
    ' ...

    If IsNumeric(part_number) then part_number = Val(part_number)
   
    Set rng1 = Application.Worksheets("Sheet1").Range("A2:A20")
' etc
End Sub