Excel 2016 VBA - Creating Dashboard with greater efficiency

Question

I'm currently in the process of building a dashboard in excel 2016 using VBA and macros. It basically involves selecting buttons on a single sheet to show/hide different chart elements whose data sources will be linked to another sheet. Its going well so far, but I've run into efficiency issues. The first issue has to do with grouping objects and the second to do with repetition of vba code.

1) Is there any way to cross-group shape objects? Right now it seems that I have to create duplicates for objects which will not be visible, and for which each parent group of objects will be hidden. Unfortunately, I cannot find a way for Excel to cross group. So if I want a group A and B and a group A and C, I can only apparently have one group (A&B or A&C, but not both). This forces me to duplicate objects so I have one pair for A&B and another for A&C. The problem is this can get quite tedious and a nightmare in organizing my objects properly when I get several dashboard buttons and sub-sections.

2) Could anyone advise on how I can make the following code more efficient so there is no repeating of code? It basically highlights a group of shapes that must show and hide with respect to what button is pressed. But right now, I have to write all the groups that must stay visible and hidden. Below is a sample of the code. Please see the True and False arguments below to get an idea of what the issue is:

   Sub Pic_1_SA_click()
   ActiveSheet.Shapes("Group 23").Visible = True
   ActiveSheet.Shapes("Group 71").Visible = False
   ActiveSheet.Shapes("Group 19").Visible = False
   ActiveSheet.Shapes("Group 20").Visible = False
   End Sub

   Sub Pic_1_SB_click()
   ActiveSheet.Shapes("Group 23").Visible = False
   ActiveSheet.Shapes("Group 71").Visible = True
   ActiveSheet.Shapes("Group 19").Visible = False
   ActiveSheet.Shapes("Group 20").Visible = False
   End Sub

   Sub Pic_2_SA_click()
   ActiveSheet.Shapes("Group 23").Visible = False
   ActiveSheet.Shapes("Group 71").Visible = False
   ActiveSheet.Shapes("Group 19").Visible = True
   ActiveSheet.Shapes("Group 20").Visible = False
   End Sub

   Sub Pic_2_SB_click()
   ActiveSheet.Shapes("Group 23").Visible = False
   ActiveSheet.Shapes("Group 71").Visible = False
   ActiveSheet.Shapes("Group 19").Visible = False
   ActiveSheet.Shapes("Group 20").Visible = True
   End Sub

Any guidance on these two issues will be greatly appreciated. Thanks!

Code review/efficiency requests belong on codereview.stackexchange.com. — user4039065
many of us behind firewalls can't see pictures - please post your actual code. For your original question - Hold your objects in arrays of shapes in your code and then hide/unhide every element in an array. — Harassed Dad
Thanks for the suggestion. I'm looking the arrays. I've edited the OP to directly show the code as well. — Matlas

pascal sautot pascal sautot · Accepted Answer · 2018-07-03T13:48:25

I simple code refactoring should do it

Sub Pic_SA_click(ByVal sShapeName As String)
    If (sShapeName = vbNullString) Then Exit Sub

    Dim oShp As Shape, s As Shape
    Set oShp = ActiveSheet.Shapes(sShapeName)
    If (oShp Is Nothing) Then Exit Sub
    oShp.Visible = True
    With ActiveSheet
        For Each s In .Shapes
            If (s.Name <> oShp.Name) Then
                s.Visible = False
            End If
        Next s
    End With
End Sub

Sub Pic_1_SA_click()
    call Pic_SA_click("Group 23")
end sub 

Sub Pic_1_SB_click()
    call Pic_SA_click("Group 71")
End Sub

Sub Pic_2_SA_click()
    call Pic_SA_click("Group 19")
End Sub

Sub Pic_2_SB_click()
    call Pic_SA_click("Group 20")
End Sub

Excel 2016 VBA - Creating Dashboard with greater efficiency

1 Answers