2 combo boxes with vlookup in the same userform that interact with each other, tboxes

  • 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:

  • Re: 2 combo boxes with vlookup in the same userform that interact with each other, tb

    !!! After 1 week of working on this problem, I just thought of something (sort of) new that actually solved my issue. It was a super simple thing, as I was expecting. I am adding the solution here in case anybody else in the future stumbles upon this thread with a similar issue.

    REAL PROBLEM: Vlookup doesn't work when numbers are used as the reference value
    SOLUTION: Convert numbers to text by selecting them > clicking on 'Data' > clicking on 'Text to Columns' > format as delimited TEXT ... this converts all of the numbers to text by adding ' at the start of each number, and now vlookup will work

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!