Bottom Line: In this episode of the Ultimate Excel Dashboard Tutorial Series you will learn how to create custom interactive info buttons for your Excel dashboard.
Skill Level: Advanced
Video Tutorial
Disclosure: Some of the links below are affiliate links, meaning, at no additional cost to you, I will earn a commission if you click through and make a purchase.
The ultimate Excel Dashboard Tutorial Series
In our Ultimate Excel Dashboard tutorial series you will learn how to create a state-of-the-art interactive Excel Dashboard with many outstanding and custom-built features. This tutorial series will cover how to
- create a basic interactive Excel dashboard with pivot charts and slicers
- design a beautiful dashboard background and gradient tile design
- create a modern dashboard slicer design
- auto-refresh dashboard pivot charts when source data changes
- create interactive dashboard info buttons [you are here]
- create interactive dashboard tabs
- create a custom interactive settings menu with modern radio and toggle buttons
By completing this tutorial series you will learn how to visualise your data and bring it to life in a completely stunning way. The great variety of different topics covered during the process will develop and strongly improve your overall Excel skillset.
Info: In this dashboard we use features that are only available in the latest version of Excel (included in Microsoft 365). If you want to are still using an older version of Excel, we recommend to upgrade to Microsoft 365 to have full access to all features.
Different Ways to Create Interactive Dashboard Info Button
In this tutorial we will show you three different ways to implement custom interactive info buttons that allow you to display additional information in your dashboard without requiring additional space. The three options differ from each other with regard to simplicity vs. scalability. That means your choice on which option to actually implement should depend on how many of these info buttons you plan to have in your interactive Excel dashboard. The important thing to mention here is, all three options do not differ from each other with regard to visual appearance or functionality, so no worries. You cannot make a wrong decision. All three options will be based on a VBA script.
Option 1: Simple, but not scalable
The first option is perfect if you just want to try this out or don’t plan on having more than one of these info buttons in your interactive Excel dashboard. It requires the following Excel shapes:
- 2 Info Button Shapes (one for the active state of the button and one for the inactive)
- 1 Info Box Element (consisting of a semi-transparent rectangle and a text box grouped together)
- Info_Button_Deliveries_Inactive
- Info_Button_Deliveries_Active
- Info_Box_Deliveries
Now that we have defined these names, we can start writing the VBA script in the Visual Basic Editor. In the left part of the Editor you have to double click on the Dashboard worksheet reference in order to open a worksheet related empty script. In there, we now will create two macros (or subs as they are called in there), one for each button shape.
‘Option 1: 2 Button Shapes | 2 Simple Macros
Sub Display_Info_Box_Deliveries ()
With ActiveSheet
.Shapes(“Info_Button_Deliveries_Inactive”).Visible = False
.Shapes(“Info_Button_Deliveries_Active”).Visible = True
.Shapes(“Info_Box_Deliveries”).Visible = True
End With
End Sub
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Sub Hide_Info_Box_Deliveries ()
With ActiveSheet
.Shapes(“Info_Button_Deliveries_Inactive”).Visible = True
.Shapes(“Info_Button_Deliveries_Active”).Visible = False
.Shapes(“Info_Box_Deliveries”).Visible = False
End With
End Sub
Now we only have to assign both macros to the button shape. The Display_Info_Box_Deliveries macro will be assigned to the inactive button shape and the Hide_Info_Box_Deliveries macro will be assigned to the active button shape. After that, the only thing to make sure is to perfectly overlay both info button shapes in order to create the illusion of them being one and the same button that just changes its appearance.
Option 2: Intermediate, more scalable
Now this section option is still based on the same shapes, so we still have two button shapes (one for the active state of the button and one for the inactive state of the button) and one info box element. But this time in the VBA script, we will outsource the code for the core functionality into a separate function in order to increase scalability. The core functionality for this use case is nothing else but making these elements appear and disappear and in the new function we will set this process up in a more generic way. There are two good reasons why using a separate function for this core functionality makes sense if you plan to have multiple info buttons in your dashboard:
- Scaling is much easier and less prone to errors
- Functionality can be changed or fixed much faster
So let’s get right into the code.
‘Option 2: 2 Button Shapes | 1 Function | 2 Simple Macros
Function Change_Info_Box_Visibility (Info_Button_Active As Object,
Info_Button_Inactive As Object, Info_Box As Object, Visible As Boolean)
If Visible = True Then
Info_Button_Inactive.Visible = False
Info_Button_Active.Visible = True
Info_Box.Visible = True
Else
Info_Button_Inactive.Visible = True
Info_Button_Active.Visible = False
Info_Box.Visible = False
End If
End Function
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Sub Display_Info_Box_Deliveries()
With ActiveSheet
Call Change_Info_Box_Visibility(
.Shapes(“Info_Button_Deliveries_Active”),
.Shapes(“Info_Button_Deliveries_Inactive”),
.Shapes(“Info_Box_Deliveries”),
True)
End With
End Sub
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Sub Hide_Info_Box_Deliveries()
With ActiveSheet
Call Change_Info_Box_Visibility(
.Shapes(“Info_Button_Deliveries_Active”),
.Shapes(“Info_Button_Deliveries_Inactive”),
.Shapes(“Info_Box_Deliveries”),
False)
End With
End Sub
With this code, you only have one boolean (True or False) value in each macro, so when you copy it over and adjust it for another button, it will be pretty hard to make any mistakes as you only have to adjust the object names and check if this one boolean value is set correctly.
Option 3: Advanced, fully scalable
Option 2 is already a good improvement compared to Option 1 with regard to scalability. But with Option 2, we still have the problem of many shapes involved that have to be duplicated, renamed (both in the Excel file and the VBA script), and repositioned. It would be much easier if we were able to only use one shape for the button instead of two. That would cut a lot of the work when duplicating and only require one macro for this one button shape. But how to realise that? We have two issues to handle here:
- How to make one button shape behave like the two button shapes behaved before (changing each others visibility)?
- With only one macro assigned to the button, how the macro know what operation (display or hide) to do next?
The solution to that problem are properties of the button shape. If you have a accessible shape property that (encodes) represents the current button state, you can easily derive a simple rule based on that property. A property we want to be changed either way is the color of the button, so why not use this property as decision foundation?! We start by deleting the inactive button shape in the Excel sheet and renaming the active button shape into Info_Button_Delivieres. The required code then looks as follows.
‘Option 3: 1 Button Shape | 1 Function | 1 Macro
Function Change_Info_Box_Visibility (Info_Button As Object,
Info_Box As Object, Visible As Boolean)
If Visible = True Then
Info_Button_Inactive.Fill.ForeColor.RGB = RGB(255,255,0)
Info_Box.Visible = True
Else
Info_Button_Inactive.Fill.ForeColor.RGB = RGB(255,255,255)
Info_Box.Visible = False
End If
End Function
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Sub Change_Info_Box_Deliveries_Visibility()
With ActiveSheet
If .Shapes(“Info_Button_Deliveries”).Fill.ForeColor.RGB = RGB(255,255,255) Then
Call Change_Info_Box_Visibility(
.Shapes(“Info_Button_Deliveries”),
.Shapes(“Info_Box_Deliveries”),
True)
Else
Call Change_Info_Box_Visibility(
.Shapes(“Info_Button_Deliveries”),
.Shapes(“Info_Box_Deliveries”),
False)
End If
End With
End Sub
This Option is the most advanced but also most scalable one. You only have two objects to duplicate, rename and reposition in the worksheet, and only one macro to duplicate and adjust the object names in.
Conclusion
That’s already it for this fifth episode of the Ultimate Excel Dashboard tutorial series. We recommend to continue with the next episode of this Ultimate Excel Dashboard tutorial series in order to make this dashboard even more awesome than it already is.
You can download the Basic Dashboard Excel File in our Download Area.
