Excel 2007 VBA: Having trouble ungrouping a newly loaded object

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

Code:

---

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

Quote:

---

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

---

Put the troublesome shape in a workbook and post it

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

Thank you for your help...

Mike

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

Code:

---

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

---

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

Code:

---

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

---

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

Code:

---

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

---

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.

Code:

---

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

Code:

---

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

---

or

Code:

---

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

---

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:

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

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

Code:

---

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.

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