Excel VBA trying to edit visibility of shapes inside a group

Question

I have create a project in excel 2013 that shows and hides a large list of shapes (network icons and text boxes) based on the data in the cells at the top of the sheet. My goal was to create a GUI for me to enter network information and as I enter the values into the cells it populates into the network drawing below. I have this all working, but I went to group every shape (visible and hidden) of the drawing into one large group so I could copy/paste it into power point for a presentation. But once I grouped the shapes I am getting a Run-time error '438': Object doesnt support this property method.

I am trying to find a way to continue manage these fields but just within a giant group. Any help would be great! Oh and the name of the master group shape is 'Group10'.

This is a quick sample of the code I have to update the network design while entering information. Also I am typing this by hand from an offline system, so mind type-os.

Private Sub WorkSheet_Change (ByVal Target As Range)
    If Range("s26").Value <> "" And Range("g26").Value <> "" Then
        ActiveSheet.Shapes("cloud1-group-p1").Visible = True
        ActiveSheet.Shapes("router1-group-p1").Visible = True
        ActiveSheet.Shapes("line1-group-p1").Visible = True
    Else
        ActiveSheet.Shapes("cloud2-group-p3").Visible = False
    End If
End Sub

I also tried to see about creating an array of all the known shapes which I called x. Then make a filter to copy only the shapes in x into a new array, y, if the shape was visible. Then copy y. But I am horrible with arrays. So I have up on that LOL

score 3 · Answer 1 · answered Mar 14 '16 at 00:23

3

There is a neat way to hide/unhide a group of shapes. Say we have three rectangles on a sheet like:

The following loop technique will work:

Sub dural2()
    ary = Array("Rectangle 1", "Rectangle 2", "Rectangle 3")
    For Each a In ary
        ActiveSheet.Shapes(a).Visible = True
    Next a
End Sub

works just fine. However trying to treat them as a group like:

Sub dural()
    ary = Array("Rectangle 1", "Rectangle 2", "Rectangle 3")
    ActiveSheet.Shapes(ary).Visible = True
End Sub

will fail. The correct way to avoid the loop is with a ShapeRange:

Sub dural3()
    Dim shr As ShapeRange, s As Shape
    Dim ary()
    i = 1
    For Each s In ActiveSheet.Shapes
            ReDim Preserve ary(1 To i)
            ary(i) = s.Name
            i = i + 1
    Next s
    Set shr = ActiveSheet.Shapes.Range(ary)
    shr.Visible = True
End Sub

answered Mar 14 '16 at 00:23

Gary's Student

95,722
10
59
99

This would work if I was trying to hide/show everything correct? I am trying to show select shapes and hide select shapes based off the information I enter for the network design. Think of it as if I have 4 devices with 3 different routes to the devices. Based off the information I enter, shapes show and hide based off the information, never showing or hiding everything at once. What I want is to be able to only copy a select amount of the shapes shown (some are side notes to the side that I dont need to copy). I need to copy this range to paste the shapes into power point for briefs ;) – Legenis Mar 14 '16 at 12:43
@Legenis ***You are correct!!*** You must **dynamically** create the `ShapeRange` based off the information you enter.............that way the code will show/hide the correct sub-set of `Shapes`. – Gary's Student Mar 14 '16 at 12:50
Is there a way to comb through an array containing a range of shapes, and as the loop combs through the array if a shape's visibility = true then is gets added to a second array? – Legenis Mar 14 '16 at 16:03
Then I could build a master array that pushes all the visible shapes into the second array. Then make a button to select/copy every shape in array two. – Legenis Mar 14 '16 at 16:17

score 0 · Answer 2 · answered Mar 14 '16 at 03:40

0

To access a Shape within a group use GroupItems like this

Sub Demo()
    Dim ws As Worksheet
    Dim ShapeGroup As Shape
    Dim AShape As Shape

    Set ws = ActiveSheet
    Set ShapeGroup = ws.Shapes("Group10")
    Set AShape = ShapeGroup.GroupItems("ShapeToHide")
    AShape.Visible = False
End Sub

answered Mar 14 '16 at 03:40

chris neilsen

52,446
10
84
123

I am having issues getting this to work, keeps getting hung up on "Set AShape = ShapeGroup.GroupItems("ShapeToHide")" for Run-time error '1004' This item with the specified name wasnt found. – Legenis Mar 14 '16 at 12:35
You did change "ShapeToHide" to the name of your shape, right? – chris neilsen Mar 15 '16 at 06:22
So I put every shape in an array and copied the array. It only copies the visible shapes. – Legenis Mar 18 '16 at 01:12

Excel VBA trying to edit visibility of shapes inside a group

2 Answers2