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:
I am now using this:
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:
X = cboMBlows.Value
If I know the sheet name and the combobox name, I would assume I'd use this:
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:
? Sheets("Mar Input").Shapes(10).Name
Next I tried recording a macro of me moving the combobox, to see what sort of code it would generate. I got this:
Okay, so then I tried this:
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?