Hello!
I am quite new to VBA, and everything I know I've taught myself... so sometimes I miss "simple" things that I hadn't encountered before. This may be one of those things.
Bakcground: I created a userform so that employees at my company are able to more easily complete their daily work diary. The form includes many fields, some related to the employee(seller) and some related to the customer. On the customer side, I have 2 combo boxes and multiple texboxes: 1 combo contains customers' names and the other 1 contains customers' numbers.
What I want: To be able to choose a name OR a number and have the rest of the customer's information populate on the rest of the fields.
The problem: The first combo box 'combo_clientname' and its corresponding vlookup code works perfectly BUT the second combo 'combo_clientnumber' isn't working even though the code is basically identical. While the list of clients' numbers does show, the vlookup returns nothing.
The error Excel is giving me is: Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class (normally I add "On Error Resume Next" in vlookups in case somebody hits backspace or something it doesn't give them this error, but I deleted this line to see what Excel would tell me about the error it was encountering... and it gave me this one)
Things I have tried: Removing the first vlookup formula in case I couldn't have 2 lookups in the same userform, formatting client number as "general" in the range itself in case being formatted as "number" was what was causing the error, reading and reading and reading through many different forums to no avail - I can't figure out what I am doing wrong!
Here is my code:
Private Sub combo_clientname_Change()
On Error Resume Next
Me.combo_clientnumber.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 2, False)
Me.tbox_association.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 3, False)
Me.tbox_clienttype.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 4, False)
Me.tbox_clientrate.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 5, False)
Me.tbox_contact.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 6, False)
Me.tbox_address.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 7, False)
Me.tbox_city.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 8, False)
Me.tbox_province.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 9, False)
Me.tbox_country.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 10, False)
Me.tbox_phone.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 11, False)
Me.tbox_email.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 12, False)
End Sub
Private Sub combo_clientnumber_Change()
Me.combo_clientname = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 2, False)
Me.tbox_association.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 3, False)
Me.tbox_clienttype.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 4, False)
Me.tbox_clientrate.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 5, False)
Me.tbox_contact.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 6, False)
Me.tbox_address.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 7, False)
Me.tbox_city.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 8, False)
Me.tbox_province.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 9, False)
Me.tbox_country.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 10, False)
Me.tbox_phone.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 11, False)
Me.tbox_email.Value = WorksheetFunction.VLookup(Me.combo_clientnumber.Value, Range("lookup_clientnumber"), 12, False)
End Sub
Display More