views:

348

answers:

2

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.

A: 

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.

AMissico
When I run MsgBox Worksheets("SheetName").Shapes("CboYear") I get "Object does not support property or method"
Ten Ton Gorilla
+2  A: 

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

Dick Kusleika
Unsupported/deprecated or just hidden?
AMissico
You can get the Hidden classes to show in IntelliSense by changing the "Show Hidden Members" property within the "Object Browser". Right-click anywhere within the "Object Browser" and check "Show Hidden Members"
AMissico
That worked, thank you.
Ten Ton Gorilla
Maybe 'hidden' is the proper term.
Dick Kusleika