VBA: How To Reference ComboBox Object

Question

I have created a lone ComboBox on the first worksheet in Excel. However, I can't find a way to reference its object.

I inserted a module, and one of my subs can successfully reference the following value:

Sheets("Sheet1").Name

However, the following is not available and throws an error:

ComboBox1.Value

Error message is: Run-time error '424': Object required

Can someone explain how to reference this ComboBox in my worksheet? Thanks

Solution

That depends on the type of combo box that you created:

• Form Control: Sheets("Sheet1").DropDowns(1)
• ActiveX Control: Sheets("Sheet1").ComboBox1

Sheets("Sheet1"). can be omitted if the code is placed inside the VBA module Sheet1.

Also see What is the difference between "Form Controls" and "ActiveX Control" in Excel 2010?

Comments

[Vipul Karkar]

try : ActiveSheet.OLEObjects("ComboBox1").Object.Value