UtterAccess Discussion Forums > Refer To Combobox Within The Shapes Collection

Full Version: Refer To Combobox Within The Shapes Collection

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

doctor9

May 14 2012, 02:24 PM

This is somewhat related to this previous topic I started.

I have changed the VBA code in an old workbook so that every reference to a worksheet is via the sheets collection. In other words, instead of this:

CODE

Main.Delete

I am now using this:

CODE

Sheets("Main").Delete

I'd like to take the strategy a step further, and do the same for all of the ActiveX controls on the worksheets.

For example, to get the value of a combobox named "cboMBlows", I normally use this:

CODE

X = cboMBlows.Value

If I know the sheet name and the combobox name, I would assume I'd use this:

CODE

X = Sheets("Mar Input").Shapes("cboMBlows").Value

But it returns this error:
Run-time error '438':
Object doesn't support this property or method

I am sure I'm close because this returns the name of the combobox properly in the Immediate Window:

CODE

? Sheets("Mar Input").Shapes(10).Name
cboMBlows

Next I tried recording a macro of me moving the combobox, to see what sort of code it would generate. I got this:

CODE

ActiveSheet.Shapes.Range(Array("cboMBlows")).Select
ActiveSheet.Shapes("cboMBlows").IncrementLeft 57

Okay, so then I tried this:

CODE

X= Sheets("Mar Input").Shapes.Range(Array("cboMBlows")).Value

Same error message as before.

Does anyone know the syntax for referring to a combobox's value, when referring to the combobox itself within the Shapes collection?

What bit of syntax am I missing?

Dennis

doctor9

May 14 2012, 02:51 PM

Arrgh. Found it at the bottom of the Help file for Object collection:

CODE

Sheets("Mar Input").OLEobjects("cboMBlows").Object.Value

I refuse to comment on the non-intuitive-ness of this solution.

Dennis

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.