Dependant Text Box value

[karolina1406]

Can someone help in writing a code for a USer Form?

I have a table called "Products" with Product codes and Quantity. I have ComboBox1 with the unique product name and I want Textbox1 to be populated with the Quantity from the "Products" table based on the ComboBox1. Kind of vlookup formula in vba

any help much appreciated

 

[DanteAmor]

Try this

Private Sub ComboBox1_Change()
  TextBox1 = ""
  If ComboBox1 = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
  TextBox1 = WorksheetFunction.VLookup(ComboBox1.Value, Range("Products"), 2, 0)
End Sub

 

[karolina1406]

Try this

Private Sub ComboBox1_Change()
  TextBox1 = ""
  If ComboBox1 = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
  TextBox1 = WorksheetFunction.VLookup(ComboBox1.Value, Range("Products"), 2, 0)
End Sub

that's lovely, thank you very much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[karolina1406]

I'm glad to help you. Thanks for the feedback.

sorry to bother you but i opened the spreadsheet today and i suddenly got a Run-time error 1004 : Unable to get Vlookup property of the WorksheetFunction class. Any idea why??

 

[DanteAmor]

That is because the combo data does not exist in the range.

Try this:

Private Sub ComboBox1_Change()
  Dim res As Variant
  TextBox1 = ""
  If ComboBox1 = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
  On Error Resume Next
  res = Application.VLookup(ComboBox1.Value, Range("Products"), 2, 0)
  If IsError(res) Then
    MsgBox "No match"
  Else
    TextBox1 = res
  End If
End Sub