Vba Listbox Lookup To Populate Another Listbox

  • Hello


    I have 1 listbox (lisbox1) that retrieve it's list items from a worksheet range (imported/database query from access). This works fine.


    I have a second listbox (listbox2) that should display results from clicking a value in listbox1.
    Listbox1 contains companynames (1 column), listbox2 needs to be populated with quotes.


    Range A3:D4800 contains company ID's, Company names, Quote Numbers. When I select a company name in listbox1, I need listbox2 to be populated with all quotes for that company.


    I have tried (using vba) to do a vlookup using the listbox1 value, but I cannot seem to figure out how to populate listbox2 with "all" quotes. I get 1 quote and that's it. I realize I probably need to have the vlookup loop through each cell in the range to find the value, but when I try this, I get a type mismatch when using the .additem (only for the 2nd and subsequent passes).


    any help would be great - thank you


    Terry

  • Re: Vba Listbox Lookup To Populate Another Listbox



    Thank you
    Terry

  • Re: Vba Listbox Lookup To Populate Another Listbox


    Sorry, I have a typo: Here is the code

  • Re: Vba Listbox Lookup To Populate Another Listbox


    I suspect it has to do with your vlookupval variable being dimmed as a variant.


    Probably in the second pass it returns an error and the variant classification allows the variable to assume the error type... in which case, the listbox cannot have an error added to it (so it throws type mismatch).


    Maybe try dimming it as a String instead.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Vba Listbox Lookup To Populate Another Listbox


    I am a bit confused with how you are expecting the code to work. How is range "Companies" defined? I assume that the first column of the range is the column with the company names, because that's your search string, but your VLOOKUP call is looking in the fourth column of the range. That doesn't quite sync up with your description

    Quote

    Range A3:D4800 contains company ID's, Company names, Quote Numbers.


    Also, you have probably figured out by now that VLOOKUP returns a single value, just as if you used it in a worksheet.


    In your loop you are offsetting your range "rng" by one column on each pass. I can't figure out why you are doing that; maybe you meant to offset by one row. Even if you offset by one row instead, you will have overlap with the range where the last value was found using VLOOKUP. For example, on the first pass you begin in row 1 and if you find the company in row 10, then on the next pass you begin your search in row 2, you will find the same value in row 10 again.


    I think VLOOKUP is an awkward solution here. I would do something like this using the Find and FindNext methods, which are more suited to the task. Note that I have not tested this for your situation.


  • Re: Vba Listbox Lookup To Populate Another Listbox


    The code runs through only once and adds only 1 item to the listbox. There are some companies that have 20 or so quotes, but do not get displayed in the listbox.


    The added code near the end (if not cl.offset(0,1).value = "None") simply says that if the value of the quote ID is "None" then it will not be added.



    Thank you
    Terry

  • Re: Vba Listbox Lookup To Populate Another Listbox


    Hi terry,


    I reworked six strings code

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: Vba Listbox Lookup To Populate Another Listbox


    Instead of .Find, you could use auto-filter

  • Re: Vba Listbox Lookup To Populate Another Listbox



    try

  • Re: Vba Listbox Lookup To Populate Another Listbox


    Thank you for all your help on this. I have thing working "Ok" but still can't get the company listbox to anything but null.


    I noticed the reason why it was only going through the code once was because I was looking in the wrong column.


    If I change the type of sub from a _change() I can step through the code and it does what it is suppose to.


    Here is the code I got working, but when the sub name is a _change() it errors out on having a null value for the lbcompanies listbox.



    Thank you
    Terry

  • Re: Vba Listbox Lookup To Populate Another Listbox


    Hello


    I did get the code working fine: Thanks to you all.
    I am now faced with another issue.


    On this same workbook, I have a linked datasource that brings all information from the company DB (access).


    Based on the company name selected in the lbcompany listbox, I now need to look that value up on Sheet2 and then offset the cell to find firstname, lastname etc values to display on Sheet1.


    Im not sure why, but I get a "Application defined or object defined" Error (runtime 1004).


    I am taking the cl value from my code and passing to to a functions to do the find. Here's the code in it's entirety:



    Thank you
    Terry

Participate now!

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