[RESOLVED] [Excel VBA] How to find if shape group exists

[drmerby3737]

I am trying to ungroup all shapes in a worksheet. I start with a copied drawing from Visio and that creates one group in Excel. I ungroup this and then there are some shapes, and another group. I ungroup that one, and there are more shapes and another group. Problem is, I'm not sure how many times to do this. I want to know how to check if a group exists within the shapes, then ungroup if it does exist.

Thanks for your help!

[koolsid]

Welcome to the forums

In Visio with a Shape object, you can check to see if the shape is an instance of a master. Something
like this...

Sub Sample()
    If Shape.Master Is Nothing Then
        '~~> Code here to ungroup
    End If
End Sub

[drmerby3737]

Thanks for the response! I am looking for a solution using Excel VBA, not Visio. I apologize if my post was confusing. Any chance there is a way to check this using Excel VBA? I am also trying to find out if there is a need to ungroup. So I want to do a check that will tell me if there are any shape groups in the worksheet. If there are no shape groups remaining, I know to stop the ungrouping.

Thanks!

[koolsid]

Oh Ok...

Use the Shapes(1).GroupItems.Count to see if it contains any shapes...

[drmerby3737]

Still not working. Here is a code snippet of what I am trying to accomplish...

Dim s As Excel.Shape
For Each s In wSheet.Shapes
If s.GroupItems.Count > 0 Then
s.Ungroup()
End If
Next

gives this error: "This member can only be accessed for a group."

I'm assuming it must be a group to get a count. I want to know if it is a group.

[koolsid]

This works for me...

Sub Sample()
    Dim Shp As Shape, ExitLoop As Boolean
    
    ExitLoop = True
    
    On Error GoTo ErrHandler
    
    Do While ExitLoop = True
        For Each Shp In ActiveSheet.Shapes
            If Shp.GroupItems.Count > 0 Then
                Shp.Ungroup
            Else
                ExitLoop = False
            End If
        Next
    Loop
ErrHandler:
End Sub

[drmerby3737]

That worked great! Thanks for your help!