Referencing Excel-type (non-AX) controls

I've never used Excel controls (the ones from the Forms toolbar) before, so I'm a little stumped... I can declare an object variable of the control type without problems, but how do I set it to reference a control? The only way I've found (using the macro recorder, as no help is available) is
Dim s As Shape, b As Button

Set s = Sheet.Shapes("myButton")
s.Select
Set b = Selection

but there has to be a way without resorting to the Selection. Surely?! :confused:

Is this what you're after, Cas?

Set s = Sheet.Shapes("myButton")
Set b = s.OLEFormat.Object

Yeah, that's precisely what I was looking for. Thanks! :)

ETA: Is there a way that doesn't use the Shapes collection at all as well?

Ohhh, this is so cool.

The context menu in the object browser contains a "Show Hidden Members" option. If one sets that, whole new worlds of the excel object model open up! :cool: And what's even better, the option also applies to intellisense in the code window.

Using that new-found resource, I could now answer my previous question in about ten seconds: Yes, one does not have to use the Shapes collection, because there is actually a ... guess what ... Buttons collection. D'uh.
Dim b As Button
For Each b In Sheet.Buttons
'...
Next

What do you mean, I'm easily excited?! :p

ps: Still no fmShiftState, though...

pps: Apart from the excel-controls collections, the main hidden groups are toolbar and menu collections (I don't know how much use these are, haven't used the CommandBar objects enough to know if they have significant limitations) and a mysterious "Modules" collection that I don't know what to make of.

According to Microsoft,
The Modules collection is present in Microsoft Excel 2000 for backward compatibility with earlier versions. However, the Count property of the Modules collection only counts Excel 5.0 and Excel 7.0 module sheets. It does not count new module sheets in the Visual Basic Editor in Excel 2000. The new collection for this purpose is VBComponents.

In general, anything that is hidden by default in the object browser (but still available to be called) is a feature that is being depreciated but is still callable for backwards compatibility reasons. Use them with caution...

That said, the Sheet.Buttons collection example used above is very valuable and I would personally expect it to remain in all future VBA versions. (This is just my opinion though, I'd have no way of knowing one way or the other.)

Thanks for the info, shg, was too lazy to look myself. :o
In general, anything that is hidden by default in the object browser (but still available to be called) is a feature that is being depreciated but is still callable for backwards compatibility reasons.
Makes sense. And then, there are also some items that I don't think have any worldly business in an exposed interface, like the Application's "Dummy101" property or the "_WSFunction" worksheet function that gives an error for all argument lists. How did we ever live without THOSE? :huh:

ETA: On a serious note, though, I'm pretty sure the Button type and Buttons collection are hidden because the non-AX controls themselves are only there for compatibility reasons - the AX control set supersedes them, no?

ETA: On a serious note, though, I'm pretty sure the Button type and Buttons collection are hidden because the non-AX controls themselves are only there for compatibility reasons - the AX control set supersedes them, no? You could be right, this could be the reason for the mentality here, I just don't know.

But from my own personal point of view, I often prefer the Excel Form controls to the ActiveX controls. The Form controls do not have as many property settings, but they do directly link to spreadsheet cell values. For a minimal programming environment, these really can be ideal.

Also in Excel 2007, the Form controls not only continue to be supported on the Worksheet, but they are also still present on the Ribbon within the Developer tab, right along with the ActiveX controls -- literally within the same menu. So I'm not sure that it's fair to consider the Form control to be depreciated in any way (yet).

That said, the Buttons collection and the like are now 'hidden members', so it's difficult to say for sure. They certainly have not been officially depreciated (to my knowledge), unlike, say, the Office Web Components, which officially saw their last version as of Excel 2003 (11.0), with mainstream support through 2011 and extended support through 2016. (The older OWC versions 9.0 and 10.0 expire earlier, I'm pretty sure.)

You could be right, this could be the reason for the mentality here, I just don't know.
I think so; it has the typical Microsoft-ambivalence flavour about it: Someone high up in the hierarchy decides to phase something out, the system programmers are instructed to provide support for old code (hidden members) but make access to it without hidden members as annoying as possible (Shape.OLEFormat.Object interface) to discourage app programmers from using it.
Then, feedback from app programmers makes the powers-that-be realize that phasing out this particular thing might not be a good idea after all, so it's left in a limbo-state for version after version, instead of either returning to full inclusion or taking the unpopular leap and breaking compatibility. Meh.