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) — Ralph
– Ralph, Commented Mar 15, 2016 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
– Dan, Commented Mar 15, 2016 at 19:15

omegastripes · Accepted Answer · 2016-03-15 19:25:55Z

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, 2016 at 19:25

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

answered Mar 15, 2016 at 19:13

Dan

4352 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dan Over a year ago

Thanks @omegastripes for removing the unnecessary With End With

veblen Over a year ago

Can I set the values in this same code set or should it be done in another module?

Dan Over a year ago

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

veblen Over a year ago

Wonderful! This has really helped!

veblen Over a year ago

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?

|

Collectives™ on Stack Overflow

Adding multiple comboboxes within code

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related