Populate Listbox2 based on Listbox1 selection

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:

Please Login or Register  to view this content.

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.
To get the list of the products for the chosen supplier, in cell G2 and copied down is this formula:

Please Login or Register  to view this content.

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.



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.