[RESOLVED] [Excel VBA] How to find if shape group exists-VBForums
Results 1 to 7 of 7

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2010
    Posts
    17

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

    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!

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,925

    Re: [Excel VBA] How to find if shape group exists

    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...

    Code:
    Sub Sample()
        If Shape.Master Is Nothing Then
            '~~> Code here to ungroup
        End If
    End Sub
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2010
    Posts
    17

    Re: [Excel VBA] How to find if shape group exists

    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!

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,925

    Re: [Excel VBA] How to find if shape group exists

    Oh Ok...

    Use the Shapes(1).GroupItems.Count to see if it contains any shapes...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2010
    Posts
    17

    Re: [Excel VBA] How to find if shape group exists

    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.

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,925

    Re: [Excel VBA] How to find if shape group exists

    This works for me...

    Code:
    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
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Apr 2010
    Posts
    17

    Re: [Excel VBA] How to find if shape group exists

    That worked great! Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.