I am trying to develop a form to track invoices as they come in. The form will have a combobox where I can click on and select a vendor number. I want the textbox to automatically fill in based on the vendor number selected from the combobox. Here's what I have so far:
Private Sub ComboBox1_Change()
'Vlookup when ComboBox1 is filled
Me.TextBox1.Value = Application.WorksheetFunction.VLookup( _
Me.ComboBox1.Value, Worksheets("Sheet3").Range("Names"), 2, False)
End Sub
Worksheet 3 is from which the information is being drawn (the vendor number and name).
When I go back to the form to test the code, I get the following error:
Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class
How do I fix this?
Try below code
I will recommend to use error handler while using vlookup because error might occur when the lookup_value is not found.
Private Sub ComboBox1_Change()
On Error Resume Next
Ret = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Worksheets("Sheet3").Range("Names"), 2, False)
On Error GoTo 0
If Ret <> "" Then MsgBox Ret
End Sub
OR
On Error Resume Next
Result = Application.VLookup(Me.ComboBox1.Value, Worksheets("Sheet3").Range("Names"), 2, False)
If Result = "Error 2042" Then
'nothing found
ElseIf cell <> Result Then
MsgBox cell.Value
End If
On Error GoTo 0