Why does grouping shapes causes vba run-time error?

haw

Board Regular
Joined
Jan 18, 2005
Messages
113
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 <<<
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Hello

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

haw

Board Regular
Joined
Jan 18, 2005
Messages
113
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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.

haw said:
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 <<<
 

Forum statistics

Threads
1,147,510
Messages
5,741,577
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top