Combobox List Generation from Range

[gsandy]

I have a dynamic range (ProdList) which has two columns (Category and Product).
Adjacent to each Category is a Product. Each Category can have multiple Products.
A Category is entered into cell A1 and then a userform (with the same name as the Category) is opened. I want the combobox (cmbProd), within the userform, to list only the Products that are adjacent to the matching Categories.
How can I do this? Thanks Sandy

[DBY]

Hello Sandy
I'm not exactly sure about your sheet layout but if each Category is grouped together with the associated Product alongside, then you could perhaps use a couple of dynamic named ranges to create the Row Source for your User Form Combo Box.

In the attached example, the named range 'ProdListbyCat' is the Row Source of the Combo Box. The User Form is Initialized from the Worksheet Change Event in Cell A2. The User Form Initialize Event loads the Combo Box Row Source and User Form Caption.

Hope this gives you a few ideas.
DBY

[gsandy]

Thanks DBY that's exactly what I wanted.
Just one small addition - I want to add a textbox to show a value adjacent to the selected product.
In your example, Cat3-Prod10 would show cell value F11.
How? Thanks Sandy

[DBY]

Hi glad to have helped.
One way would be to add another named range and Index that in the text box based on the combo selection. See the amended example.

DBY

[gsandy]

Thanks again, that worked. I can now spend the rest of the day modifying to suit. Cheers Sandy