Results 1 to 7 of 7

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

Hybrid View

  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
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

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


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  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
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

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


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  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
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

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


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  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
  •  



Click Here to Expand Forum to Full Width