Efficient method of creating dashboard in Excel 2016

[Matlas]

Greetings!


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 charts whose data sources will be linked to another sheet. Its going well so far, but I've run into efficiency issues. The first issue (not to do with vba) 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 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

I''ve tried looking on line for help with these issues but no luck thus far. Any guidance will be greatly appreciated. Thanks!

[Paul_Hossler]

Assumes you're not using ActiveX buttons

This isn't a whole lot better, but you could use the same macro for all 4 buttons

Option Explicit

Sub ClickButton()
    Application.ScreenUpdating = False
    
    'MsgBox Application.Caller
    
    With ActiveSheet
        .Shapes("Group 23").Visible = False
        .Shapes("Group 71").Visible = False
        .Shapes("Group 19").Visible = False
        .Shapes("Group 20").Visible = False
    
        Select Case Application.Caller
            Case "Pic_1_SA": .Shapes("Group 23").Visible = True
            Case "Pic_1_SB": .Shapes("Group 71").Visible = True
            Case "Pic_2_SA": .Shapes("Group 19").Visible = True
            Case "Pic_2_SB": .Shapes("Group 20").Visible = True
        End Select
    End
    Application.ScreenUpdating = True
End Sub

[Matlas]

Thank you Paul! Your input is much appreciated. I'll go with the code and method you've provided.