Adding multiple comboboxes within code

Question

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.

Possible duplicate of [Programatically add ComboBox in VBA (Excel)](http://stackoverflow.com/questions/17675761/programatically-add-combobox-in-vba-excel) — Ralph, Mar 15 '16 at 18:53
@Ralph it appears that since @veblen used `OLEObjects` rather than `AddFormControl` that the solution proposed in that link wouldn't exactly work (correct me if I am wrong about that) — Dan, Mar 15 '16 at 19:15

score 2 · Answer 1 · edited Mar 15 '16 at 19:25

2

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

edited Mar 15 '16 at 19:25

omegastripes

12,351
4
45
96

answered Mar 15 '16 at 19:13

Dan

425
2
13

Thanks @omegastripes for removing the unnecessary `With` `End With` – Dan Mar 15 '16 at 19:33
Can I set the values in this same code set or should it be done in another module? – veblen Mar 15 '16 at 19:34
@veblen yea, just add `cbox1.value = "Desired Text"` below where you set the drop-downs. Alternatively, you can use `cbox1.value = cbox1.List(1)` which would set your cbox1 to the 2nd drop-down option (since Excel starts counting at 0) – Dan Mar 15 '16 at 19:57
Wonderful! This has really helped! – veblen Mar 15 '16 at 19:59
Dan, or anyone else, what I need to know how to do, is trigger code after a combobox selection is made. I have everything up to that point, but I'm just not getting that part of it. I want to be able to run code after a cbox1 and cbox2 selections are made. Any ideas? – veblen Mar 15 '16 at 21:10
@veblen please mark this as the answer to adhere to the SO process – Dan Mar 31 '16 at 20:14

Adding multiple comboboxes within code

1 Answers1