Excel – Unable to access Excel Form Objects through VBA when Items are Grouped

excelvba

Is there anyway to access (through VBA) properties of a form object (using the forms toolbar for pre-2007, and on the developer tab / insert / Form Controls for 2007) when it is grouped?

For example, normally you could access the max property of a Spinner control with the following code: 

Sheet1.Spinners("Spinner 1").Max

OR

Sheet1.Shapes("Spinner 1").ControlFormat.Max

Near as I can tell, the only way to access an item that has been grouped is by using the shapes collection, such as: 

Sheet1.Shapes("Group 1").GroupItems("Spinner 1")

The problem is that when I attempt to access a property for that control, such as

Sheet1.Shapes("Group 1").GroupItems("Spinner 1").ControlFormat.Max

I get the following error: Run-time error '1004': Unable to get the Max property of the Spinner class.

Seems like a pretty simple problem, but I'm a bit stuck. Maybe there's a way to cast the object into a spinner control object and then access it?

Any suggestions?

Best Answer

[Edit: I was mistaken. The only way this will work is by using Selection. The following code will work: 

Sheet1.Shapes("Group 1").GroupItems("Spinner 1").Select
Selection.Max = 20

Obviously this is not ideal. Any further assistance would be grand.]

After a fair bit of cajoling, I've managed to figure this one out. To access a form control that is grouped, you need to use the GroupObjects collection (a hidden member): 

Sheet1.GroupObjects("Group 1").ShapeRange.GroupItems("Spinner 1").ControlFormat.Max

Hope that helps anyone else who might run into this issue!

Related Topic