CODEWITHSUNDEEP
excelcomboboxactivexvba
veblen
veblen

Reputation: 73

Adding multiple comboboxes within code

I've been away from VBA for a few years and am trying to build multiple comboboxes within VBA code.

I can get the code working for a single combobox but I'm having issues adding a second one.

Below is my latest code. This code adds the comboboxes but it doesn't populate the dropdowns and it also generates an "Object doesn't support this property or method" error.

Any ideas or input would be highly appreciated.

Sub CreateComboBoxes()
    Dim cbox1 As OLEObject
    Dim cbox2 As OLEObject

  Set cbox1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
       Left:=20, Top:=30, Width:=100, Height:=20)

  Set cbox2 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
        Left:=150, Top:=30, Width:=100, Height:=20)

        Sheets("Dashboard").cbox1.Clear
              Sheets("Dashboard").cbox1.AddItem "NYC"
              Sheets("Dashboard").cbox1.AddItem "London"
              Sheets("Dashboard").cbox1.AddItem "Tokyo"


        Sheets("Dashboard").cbox2.Clear   
              Sheets("Dashboard").cbox2.AddItem "One"
              Sheets("Dashboard").cbox2.AddItem "Two"
              Sheets("Dashboard").cbox2.AddItem "Three"


 End Sub

Edit: The linked archived question is only for a single combobox and I'm trying to add multiple comboboxes. I can get it working for a single combobox, but am having issues getting code to create two.

Upvotes: 2

Views: 2197

Answers (1)

Dan
Dan

Reputation: 435

If you define cbox1 and cbox2 as an Object, you are able to manipulate its drop-down values. Your code should look like this:

Sub CreateComboBoxes()
    Dim cbox1 As Object
    Dim cbox2 As Object

    Set cbox1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
        Left:=20, Top:=30, Width:=100, Height:=20).Object

    Set cbox2 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
        Left:=150, Top:=30, Width:=100, Height:=20).Object

    cbox1.Clear
    cbox1.AddItem "NYC"
    cbox1.AddItem "London"
    cbox1.AddItem "Tokyo"

    cbox2.Clear
    cbox2.AddItem "One"
    cbox2.AddItem "Two"
    cbox2.AddItem "Three"

End Sub

Upvotes: 2

Related Questions