Excel 2007 VBA: Having trouble ungrouping a newly loaded object

[Spielberg]

Hello Excel gurus...

So in my continual pursuit to create puzzles and games, I have simple WMF file that is comprised of 8 freeform objects.

What I'm having trouble with is getting the object ungrouped after it's loaded. (Manually ungrouping it works with no problem.)

I tried



Set myDocument = Worksheets(1)
For Each s In myDocument.Shapes
s.Ungroup
Next


And get a "Run-time error '1004': This member can only be accessed for a group." message. But it is a group!

That code was taken right from the help file.

After it is ungrouped, I'll need to individually rename each of the 8 components within that group with a loop, based on its location within the grid. That's the easy part.

Does anyone have any ideas? Thank you in advance for you amazing help.

Mike

[Paul_Hossler]

Does anyone have any ideas? Thank you in advance for you amazing help.

Put the troublesome shape in a workbook and post it

[Spielberg]

I am not sure why I didn't do that before... I'm not very bright sometimes...

Thank you for your help...

Mike

[royUK]

Try amending this with the group name, you can see of in the NameBox when you select it.



ActiveSheet.Shapes.Range(Array("your group name here")).Ungroup

[royUK]

I've just looked at your example and this works for me


ActiveSheet.Shapes.Range(Array("Group 3")).Ungroup

[Andy Pope]

This will convert picture to a group of shapes and then ungroup the shapes



Sub UngroupPicture()
' convert picture to shapes
ActiveSheet.Shapes.Range(Array(ActiveSheet.Shapes(1).Name)).Ungroup
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Ungroup
End Sub

[snb]

Ungroup can only be applied to shapes that have been grouped.
So you will have to test the shapes whether they are grouped or not before applying .ungroup, otherwise the error message will appear.


Sub M_snb()
For Each it In Shapes
If it.Type = 6 Then it.Ungroup
Next
End Sub

Unfortunately in your file you added the grouped shape as a picture, type=13

In your uploaded file that contains only 1 grouped shape this code suffices

Sub M_snb()
For Each it In Shapes
it.Ungroup
Next
End Sub
or

Sub M_snb()
Shapes(1).Ungroup
End Sub

[Spielberg]

Thank you Mr. RoyUK and AndyPope, those actually worked. However, and this part really confuses me - when object is ungrouped, each segment (in this case) is named Freeform X, Freeform, X+1, etc. The problem is, getting this loop to rename what (I assume) should be in each case Freeform 1, Freeform 2, etc.

Mr. AndyPope: The first line of your code worked perfectly, but the second line of your code:


ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Ungroup

gave me the dreaded, but typical 1004, Application defined or object defined error.

We all know when objects are copied and pasted or are newly created shapes, the internal shape counter ticks away...

Is there a way to reset that each time (I have to load this image 300 times!) And the number is always different.

Or, is there a way to get the names of an objects "internal shapes"?

Thank you both for the awesome code!

MIke

[Andy Pope]

Using your example and this modification I get the following information reported in immediate window


Sub UngroupPicture()
Dim shp As Shape
' convert picture to shapes
Debug.Print "Original Shapes"
For Each shp In ActiveSheet.Shapes
Debug.Print shp.Name
Next
ActiveSheet.Shapes.Range(Array(ActiveSheet.Shapes(1).Name)).Ungroup
Debug.Print "Ungrouped picture"
For Each shp In ActiveSheet.Shapes
Debug.Print shp.Name
Next
Debug.Print "Ungrouped grouped shape"
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Ungroup
For Each shp In ActiveSheet.Shapes
Debug.Print shp.Name
Next
End Sub


Original Shapes
Picture 2
Ungrouped picture
Group 3
Ungrouped grouped shape
AutoShape 2
Freeform 4
Freeform 5
Freeform 6
Freeform 7
Freeform 8
Freeform 9
Freeform 10
Freeform 11


If you wanted to rename the freeform objects 1 to n then you would need to loop through the shapes once checking for Freeform types, change the name to something unique such as MyFF_nn and then go through the look again rename MyFF_nn to Freeform nn.

[Spielberg]

Thank you for that AMAZING code! I was so excited to see that! And... It worked! I never would have been able to figure that out, I don't know why.

You'd think, for someone who's been using Excel since 1990, and VBA since that started (I forget the year,) I'd know more about it.

But I don't. I do try before I post, I Google, I experiment, because there's no satisfaction greater than being able to figure out something yourself.

But I'm older now, too and the brain isn't what it used to be...

So, thank you sir, that was AWESOME!

Mike