Can't see drop down list in Excel VBA

Question

I've created a very simple drop down list in excel. It holds a sequence of integers loaded from a cell range (years). I need to read the selected value in vba. However I can't even seem to find the control!

I've tried

CboYear
Sheet2.CboYear
Worksheets("SheetName").CboYear

etc. etc.

From everywhere in VBA this control just doesn't exist. All the tutorials I find seem to assume that using just the control name will work.

I've tried the code in the sheet itself, the workbook, and a module, no luck.

Answer 1

There are two kinds of control sets used by Excel. The built-in controls and Microsoft Forms 2.0 controls. The built-in controls available through the Forms toolbar are controls specifically designed to work within Excel and use ranges. The other controls are the Microsoft Forms 2.0 controls. These are ActiveX controls that are used by Office VBA. The are designed to work within Office and are more similiar to the controls used by Windows.

Answer 2

If you got the drop down from the Forms toolbar, use

Sheet2.DropDowns("CboYear")

The DropDowns property is unsupported/deprecated so you won't get intellisense, but it still works.

If you got the drop down from the Control Toolbox, then

Sheet2.CboYear

should work