How do you correctly reference a dynamic list for the source of an Excel ComboBox?

Question

I have two ComboBoxes on my userform. The first provides options from a named list 'Category'. I typed the word Category into the row source for this ComboBox and it works fine offering drop down for all items on the Category list.

Each item on the Category list is itself a named range. I would like the second ComboBox (Supplier) to read the item selected in the first and then offer the options within the named range that is selected. At the moment it only offers the first item in each named range. I am currently using the following code for the first ComboBox.

Private Sub Category_Change()

Worksheets("Input").Range("D10") = Category.Value
'Worksheets("Input").Range("D10") = CStr(Worksheets("Input").Range("D10"))
Supplier.RowSource = Worksheets("Input").Range("D10")
End Sub

This writes the value selected for the first ComboBox to a cell and then attempts to get the second ComboBox to read this...

Any help would be magnificent!

Josh

Answer 1

You need to get the range specified by the text in cell D10, not just get the range D10.

change your code to:

Supplier.RowSource = Range(Worksheets("Input").Range("D10").Value)

Answer 2

Thanks guys. I believe you were all correct. Purely by chance I stumbled upon the cause of my problem.

My primary list was written as a column- RowSource read it fine.

My secondary lists were all written as rows- which RowSource was only pulling the first item from. Rearranging the rows to columns and updating Name Manager sorted it.

What an irritating answer...

Thanks again!