Combobox change

[stephenmcd]

Hello All

on a user form I am trying to update the info, I am using a combobox1 to enter the search data then it finds a match in column c it will pull the rest of the info the fill in the user form but i can't find out what i am doing wrong.
the yellow line is where I am getting code 13 mismatch.

Private Sub UserForm_Activate()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim b As Integer
Me.ComboBox1.Clear
Me.ComboBox1.AddItem ""
For b = 3 To sh.Range("c" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox1.AddItem sh.Range("c" & b).Value
Next b
End Sub

Private Sub ComboBox1_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("sheet1")
Dim i As Long

If Me.ComboBox1.Value <> "" Then
If VBA.IsError(Application.Match(VBA.CLng(Me.ComboBox1.Value), sh.Range("C:C"), 0)) = True Then
MsgBox "Record Not found", vbCritical
Exit Sub
Else
i = Application.Match(VBA.CLng(Me.ComboBox1.Value), sh.Range("C:C"), 0)

End If

Me.TextBox2.Value = sh.Range("f" & i).Value
Me.TextBox3.Value = sh.Range("g" & i).Value
Me.TextBox4.Value = sh.Range("h" & i).Value
Me.TextBox5.Value = sh.Range("i" & i).Value
Me.TextBox6.Value = sh.Range("j" & i).Value
Me.TextBox7.Value = sh.Range("k" & i).Value
Me.TextBox8.Value = sh.Range("l" & i).Value
Me.TextBox9.Value = sh.Range("m" & i).Value
Me.TextBox10.Value = sh.Range("n" & i).Value

Else

Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""




End If




End Sub

 

[Fluff]

The problem is you are trying to convert an alphanumeric string to a number which can't be done.
If you load the combo like

Private Sub UserForm_Initialize()

   With Sheets("Sheet1")
      Me.ComboBox1.List = .Range("C3", .Range("C" & Rows.Count).End(xlUp)).Value
   End With
End Sub

You can then use

Private Sub ComboBox1_Click()
   Dim i As Long
   
   i = Me.ComboBox1.ListIndex + 3
   With Sheets("Sheet1")
      Me.TextBox2.Value = .Range("f" & i).Value
      Me.TextBox3.Value = .Range("g" & i).Value
      Me.TextBox4.Value = .Range("h" & i).Value
      Me.TextBox5.Value = .Range("i" & i).Value
      Me.TextBox6.Value = .Range("j" & i).Value
      Me.TextBox7.Value = .Range("k" & i).Value
      Me.TextBox8.Value = .Range("l" & i).Value
      Me.TextBox9.Value = .Range("m" & i).Value
      Me.TextBox10.Value = .Range("n" & i).Value
   End With
End Sub

 

[stephenmcd]

i = Me.ComboBox1.ListIndex + 3 With Sheets("Sheet1") Me.TextBox2.Value = .Range("f" & i).Value Me.TextBox3.Value = .Range("g" & i).Value Me.TextBox4.Value = .Range("h" & i).Value Me.TextBox5.Value = .Range("i" & i).Value Me.TextBox6.Value = .Range("j" & i).Value Me.TextBox7.Value = .Range("k" & i).Value Me.TextBox8.Value = .Range("l" & i).Value Me.TextBox9.Value = .Range("m" & i).Value Me.TextBox10.Value = .Range("n" & i).Value End With End Sub

Thankyou works perfact now

 

[Fluff]

You're welcome & thanks for the feedback.