ListBox populated dynamically

Copper Contributor

Disclaimer: I'm a VBA noob, so I'm well aware that the solution to this issue is probably extremely simple.

I want to make a ListBox that populates dynamically based on what option the user selects in the ComboBox above it. I've attached an example to show how I'm currently trying to do it, but it won't compile and says that I haven't specified an object. What am I doing wrong?


Private Sub CategoryComboBox_Change()

'determine options based on selected category
Dim selectedCategory As String
Set selectedCategory = CategoryComboBox.Value

Select Case selectedCategory
    Case "Fruit"
        OptionListBox.AddItem "Apple"
        OptionListBox.AddItem "Orange"
        OptionListBox.AddItem "Banana"

    Case "Vegetable"
        OptionListBox.AddItem "Broccoli"
        OptionListBox.AddItem "Green Beans"
        OptionListBox.AddItem "Carrots"
    Case "Starch"
        OptionListBox.AddItem "Mashed Potato"
        OptionListBox.AddItem "Pasta"
        OptionListBox.AddItem "Rice"
End Select

End Sub


5 Replies

Are you specifically trying to do this with VBA as opposed to creating a dynamic drop down list with formulas?
Yeah, it's intended to be a part of a larger user form that will be used to populate a workbook
this line report error ?

if so,try

@peiyezhu it's actually the line "Set selectedCategory = CategoryComboBox.Value

re:Set selectedCategory = CategoryComboBox.Value
how about
selectedCategory = CategoryComboBox.Value

because no need to add Set for string