Forum Discussion

Carter990's avatar
Carter990
Copper Contributor
Jun 13, 2023

ListBox populated dynamically

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()

OptionListBox.Clear
'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

  

  • N1CK_R's avatar
    N1CK_R
    Copper Contributor
    Hi.

    Are you specifically trying to do this with VBA as opposed to creating a dynamic drop down list with formulas?
    • Carter990's avatar
      Carter990
      Copper Contributor
      Yeah, it's intended to be a part of a larger user form that will be used to populate a workbook
      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        this line report error ?
        OptionListBox.Clear

        if so,try
        me.OptionListBox.Clear

Resources