Populate drop down from SQL table

[Balthazar]

I want to populate a combo box from a SQL table. I have an immediate problem in that when I create the combo box I cannot refer to it. It takes a default name of dropdown1, but when I attempt to reference this in VBA using:

With Sheets("Sheet2").dropdown1

I get an error:

Run time error 438
Object doesn't support this property or method.

I also notice that if I rename the object by right-clicking it then editing the name that appears in the Excel name window, Excel still calls the control 'dropdown1'.

 

[Norie]

How did you create the combobox?

 

[Balthazar]

How did you create the combobox?

I inserted it from the 'Insert' icon in the Developer ribbon.

 

[Norie]

Try using this to get a reference to the combobox.

Dim cmb As Object

    Set cmb = Sheets("Sheet1").Shapes("Drop Down 1")

 

[Balthazar]

Try using this to get a reference to the combobox.

Dim cmb As Object

    Set cmb = Sheets("Sheet1").Shapes("Drop Down 1")

OK, that doesn't throw an error but how do I add an item to the list? AddItem doesn't do anything.