PDA

View Full Version : Efficient method of creating dashboard in Excel 2016



Matlas
07-03-2018, 05:24 AM
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
07-03-2018, 12:21 PM
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
07-04-2018, 01:20 AM
Thank you Paul! Your input is much appreciated. I'll go with the code and method you've provided. :)