CODEWITHSUNDEEP
excelcomboboxvba
James Watts
James Watts

Reputation: 41

How to create and populate an activex combobox using vba in excel.

I am having a problem when trying to create and then populate a activex combobox in vba for excel. The code below works when run as two seperate macros but when I try to put the two together an empty combobox is created. Can anybody tell me why this is and how to overcome this problem?

Thanks in advance, JW

 Sub CreateComboBox1()
    'Creating ComboBox1:
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
                Link:=False, DisplayAsIcon:=False, Left:=50, Top:=80, Width:=100, _
                Height:=15).Select
    End Sub

    Sub PopulateComboBox1()
    'Populating ComboBox1
    Sheet1.ComboBox1.AddItem "Date", 0
    Sheet1.ComboBox1.AddItem "Player", 1
    Sheet1.ComboBox1.AddItem "Team", 2
    Sheet1.ComboBox1.AddItem "Goals", 3
    Sheet1.ComboBox1.AddItem "Number", 4
    End

Upvotes: 3

Views: 48322

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Try this

Sub CreateComboBox1()
    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
                Link:=False, DisplayAsIcon:=False, Left:=50, Top:=80, Width:=100, _
                Height:=15)
        With .Object
            .AddItem "Date"
            .AddItem "Player"
            .AddItem "Team"
            .AddItem "Goals"
            .AddItem "Number"
        End With
    End With
End Sub

Upvotes: 5

Related Questions