Cascading Userform Comboboxes with Unqiue List

Hello All,

I am using a userform to allow users to select an option from 2 comboboxes (titled “SrchPackage” and “SrchComponent”). The list in the 2nd combobox, “SrchComponent”, is based on the user’s selection from the 1st combobox, “SrchPackage”.

My dataset is as follows:

ColA	ColB
A	Blue
A	Blue
A	Yellow
B	Red
B	Yellow
B	Red

Upon userform initialisation, I use the following code to populate the 1st combobox (named “SrchPackage”) with unique values from the dynamic range in ColA, also named “SrchPackage”. This code works fine.

Please Login or Register  to view this content.

Based on the dataset above, combobox “SrchPackage” would return:
A
B

If a user was to select “A”, the list in the 2nd combobox, “SrchComponent”, ideally would return the unique values from “ColB corresponding to the value “A”, i.e.:
Blue
Yellow

But, I’m having trouble with the ‘unique’ part. With the code I’m currently using below, the list generated for the 2nd combobox appears as:
Blue
Blue
Yellow

Here is the code I’m using for the Combobox “SrchPackage” change event: (the range “vlup” is a dynamic named ranged for ColA).

Please Login or Register  to view this content.

Your assistance with the issue of not being able to generate a unique list of entries in the 2nd combobox is greatly appreciated, thank you.

Ronjay24

Ronjay24

See if the attached is what you are after

JapanDave & jindon,

Thank you both for your solutions - both worked as intended. I decided to go with JapanDave's, as I was able to simply copy their code into my workbook. I had to ensure that the variable "j" was dimensioned as a Variant and not a Long.

Cheers,
Ronjay24