Ultimate Excel Dashboard | Interactive Info Button

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email

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

  1. create a basic interactive Excel dashboard with pivot charts and slicers
  2. design a beautiful dashboard background and gradient tile design 
  3. create a modern dashboard slicer design
  4. auto-refresh dashboard pivot charts when source data changes
  5. create interactive dashboard info buttons [you are here]
  6. create interactive dashboard tabs
  7. 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.

Demo Interactive Info Button-min

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)
If you have Office 365, we recommend to make use of the integrated icon library and use the info icon for the Button shapes, making the active state yellow and the inactive state white.
For any sort of custom interactive features based on a VBA script, it is crucial to not skip the step of naming the relevant objects in order to make them easy to reference and differentiable from each other in the script. You can easily rename Excel shapes via the Selection Pane.
 
As a suggestion for this use case, we give these three objects the following names (this button is placed in the Deliveries tile of the dashboard):
  • 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:

  1. Scaling is much easier and less prone to errors
  2. 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:

  1. How to make one button shape behave like the two button shapes behaved before (changing each others visibility)?
  2. 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.

You may also like the following Excel Dashboard tutorials:

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email