Why does grouping shapes causes vba run-time error?

[haw]

The following code snippet works fine for a sheet where I have several shapes (some Forms checkboxes, radio buttons, groups, etc., plus some arrows, ovals and other drawing objects). But if I group some of the shapes together (say, to move them around), then I get run time error #1004 (application defined or object defined error). From stepping through the macro in debugger, I can see that it blows up when it hits the IF statement while referencing the grouped controls.

Wonder why this is and what I can do about it?

>>> snip <<<
With Sheets("EstimatingInput").Shapes(shapeindex)
If .FormControlType = xlCheckBox Then
>>> snip <<<

 

[Glaswegian]

Hello

What code are you using to group your shapes? Are you creating a shaperange? Can you post the code that is failing?

 

[haw]

The "grouped objects" to which I refer were grouped "manually" by me using the Excel Drawing toolbar. Here's the deal: I had a sheet that fires my VB macro. The macro was working fine. Then I added a couple of "line" and "arrowed line" drawing objects to my sheet to clarify something when it prints. Then all of the sudden my macro started dying on that If statement I previously pointed out.

I actually resolved the problem this morning (after a little sleep!) by placing another IF statement ahead of the original one. Now it looks like this:
With Sheets("EstimatingInput").Shapes(shapeindex)
If .Type = 8 Then
If .FormControlType = xlCheckBox Then
...

This works fine because the "If .formcontroltype ..." statement is never executed unless the Shape is a MSForms control (I assume that's what 8 means ... any idea where I can find the actual enumerated type declaration in the online help files?)

I guess the original statement I had in there won't work if my Shape is actually an array of shapes, which is the case if I've "manually grouped" the shapes on the sheet.

Thanks for your time!

 

[tusharm]

Your code only works when used with a shape that contains a forms control. If you use it with a drawing shape such as a rectangle, it won't work. Try it, I just did. I inserted a rectangle and a checkbox from the forms commandbar. Then, from the VBE Immediate window used ?activesheet.shapes(2).formcontroltype

For the rectangle I got the app/object error. For the checkbox, I got 1.

When you group objects, XL creates a new shape containing all the grouped objects. This new shape doesn't have the formcontroltype property.

For a grouped item, use the GroupItems property as a pathway to the individual objects in the group. Continuing my test, ?activesheet.shapes(1).groupitems(2).formcontroltype
returns 1 for the checkbox in the group and app/object error for the rectangle.

The following code snippet works fine for a sheet where I have several shapes (some Forms checkboxes, radio buttons, groups, etc., plus some arrows, ovals and other drawing objects). But if I group some of the shapes together (say, to move them around), then I get run time error #1004 (application defined or object defined error). From stepping through the macro in debugger, I can see that it blows up when it hits the IF statement while referencing the grouped controls.

Wonder why this is and what I can do about it?

>>> snip <<<
With Sheets("EstimatingInput").Shapes(shapeindex)
If .FormControlType = xlCheckBox Then
>>> snip <<<