I would like to populate the 2nd List box based on the selection of the 1st List box? The product codes are unique to a supplier in my workbook. I have attached a sample file.
Thanks in advance
I would like to populate the 2nd List box based on the selection of the 1st List box? The product codes are unique to a supplier in my workbook. I have attached a sample file.
Thanks in advance
Aland2929,
Attached is a modified version of your test file.
I have provided two different solutions, a Formula solution and the requested VBA userform solution.
For the formula solution:
First I converted the data in columns A:D into a table (Selected the data -> Insert tab -> Table)
Then in cell F2 and copied down is this formula to get the list of unique suppliers:
Next I created a dynamic named range called listUnqSuppliers with this named range formula:Please Login or Register to view this content.
Cell J1 uses a data validation list with that named range to display a drop-down list of the unique suppliers.Please Login or Register to view this content.
To get the list of the products for the chosen supplier, in cell G2 and copied down is this formula:
I also created a dynamic named range called listSupplierProducts with this named range formula:Please Login or Register to view this content.
Cell J2 uses a data validation list with that named range to display a drop-down list of the products. The list only contains products for the selected Supplier. I threw in a couple of extra formulas in cells J4 and J5 to display the Code and Units for the chosen Supplier and Product.Please Login or Register to view this content.
On to the VBA portion. First I deleted the RowSource property of ListBox1, and I used the Userform_Initialize event in order to populate ListBox1:
Please Login or Register to view this content.
Next I used the ListBox1_Change event in order to populate ListBox2:
Please Login or Register to view this content.
Hopefully you can adapt one of these solutions to fit your needs.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Absolutely brilliant! Thanks so much for your time and effort. These solutions are perfect.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks