Ultimate Excel Dashboard | Interactive Settings Menu

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 a custom interactive settings area for your dashboard. This includes modern-design radio buttons for changing the dashboard color theme with one click and beautiful toggle buttons for controlling the visibility of dashboard elements.

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
  6. create interactive dashboard tabs
  7. create a custom interactive settings menu with modern radio and toggle buttons [you are here]

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. 

Building Interactive Settings Menu in Excel from Scratch

A collapsable and interactive settings menu allows you to implement all sorts of additional functionality to change the behaviour and appearance of your dashboard with only one-click. Of course, this feature cannot be find in Excel by default, but has to be built from scratch by using default Excel shapes and a little bit of custom VBA code. 

The result of this effort will be absolutely worth it, as you will know how to create these beautiful  radio and toggle buttons and enable them to control your dashboard style and behaviour.  

Creating Settings Menu Area and custom Buttons

Before writing any VBA script you need to create the settings menu area and all custom buttons based on default Excel shapes.

Settings Button

This is the button that will be placed in the header bar to make the settings menu area appear and disappear. We recommend to make use one of the icons available in integrated Icon Library with a white fill and a white slim border.

Settings Menu Area

This includes the white area plus the button area titles in it. To create the exact look you see in the upper image, use a simple rectangle and place a small triangle on top of it (white fill, no borders). For the button area titles you are free to use any font and font color you would like.

Radio Buttons for Theme Color Control

The four radio buttons are built using rounded rectangle shapes with maximum roundness.

  • Active Button State Design: Green Fill + Dark Border + White Font
  • Inactive Button State Design: 100% Fill Transparency + Dark Border + Dark Font

You may ask why we use the fill transparency for the inactive button state design instead of simply changing the fill to white. The simple answer to this is, it makes changing the visual state of the button via VBA much easier later on.

Toggle Buttons for Element Visibility Control

The two toggle buttons each consist of three separate elements: A rounded rectangle for the toggle background, a circle, and a text field.

  • Active Button State Design: Circle with dark green Fill, shadow, placed on the right side + Background Element with light green Fill + Textfield with Text “On”
  • Inactive Button State Design: Circle with white Fill, shadow, placed on the left side + Background Element with light grey Fill + Textfield with Text “Off”

VBA Code for Settings Button

The settings button is placed in the header bar and has the job (when clicked on it) to make the whole settings menu area with all buttons in it either appear or disappear. The respective VBA code for this looks as follows.

‘Settings Menu Button

 

Sub Change_Settings_Menu_Visibility()

 

    With ActiveSheet

 

        If .Shapes(“Settings_Button”).Fill.Transparency = 1# Then

 

            ‘Visibility Settings Button

            .Shapes(“Settings_Button”).Fill.Transparency = 0#

 

            ‘Visibility Settings Menu

            .Shapes(“Settings_Menu_Frame”).Visible = True

            .Shapes(“Theme_Button_1”).Visible = True

            .Shapes(“Theme_Button_2”).Visible = True

            .Shapes(“Theme_Button_3”).Visible = True

            .Shapes(“Theme_Button_4”).Visible = True

            .Shapes(“Toggle_Background_Info”).Visible = True

            .Shapes(“Toggle_Circle_Info”).Visible = True

            .Shapes(“Toggle_Textbox_Info”).Visible = True

            .Shapes(“Toggle_Background_Tabs”).Visible = True

            .Shapes(“Toggle_Circle_Tabs”).Visible = True

            .Shapes(“Toggle_Textbox_Tabs”).Visible = True

        Else

 

            ‘Visibility Settings Button

            .Shapes(“Settings_Button”).Fill.Transparency = 1#

 

            ‘Visibility Settings Menu

            .Shapes(“Settings_Menu_Frame”).Visible = False

            .Shapes(“Theme_Button_1”).Visible = False

            .Shapes(“Theme_Button_2”).Visible = False

            .Shapes(“Theme_Button_3”).Visible = False

            .Shapes(“Theme_Button_4”).Visible = False

            .Shapes(“Toggle_Background_Info”).Visible = False

            .Shapes(“Toggle_Circle_Info”).Visible = False

            .Shapes(“Toggle_Textbox_Info”).Visible = False

            .Shapes(“Toggle_Background_Tabs”).Visible = False

            .Shapes(“Toggle_Circle_Tabs”).Visible = False

            .Shapes(“Toggle_Textbox_Tabs”).Visible = False

       

        End If

 

    End With

 

End Sub

VBA Code for Radio Buttons (Theme Color Control)

For the Radio Buttons we have to take a look at two different functional requirements:

  1. Buttons’ Visual State: We want the radio buttons themselves to change their visual appearance (as a group) correctly. 
  2. Theme Colors: We want the theme colors to change based on which button is selected. 

In order to be able to switch between different theme colors, we have to create multiple designs that will then be either displayed or hided via the radio buttons. To make the creation and design process as simple as possible, we start by grouping the header bar and tile design shape of our default dashboard together and name it ‘Dashboard_Theme_1’. After that, we can simply duplicate this theme group three times, adjust the color designs of the duplicates, and name the new theme designs accordingly with incrementing numbers. 

The VBA code for the Radio Buttons can then be realised as follows.

‘Color Theme Buttons

 

Function Change_Radio_Button_Status (Radio_Button As Object, Active As Boolean)

 

    With Radio_Button

 

        If Active = True Then

            .TextFrame.Characters.Font.Color = RGB(255, 255, 255)

            .Fill.Transparency = 0#

        Else

            .TextFrame.Characters.Font.Color = RGB(0, 0, 0)

            .Fill.Transparency = 1#

        End If

 

    End With

 

End Function

 

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

 

Sub Display_Theme_1()

 

    With ActiveSheet

 

        ‘Dashboard Themes Visibility

        .Shapes(“Dashboard_Theme_1”).Visible = True

        .Shapes(“Dashboard_Theme_2”).Visible = False

        .Shapes(“Dashboard_Theme_3”).Visible = False

        .Shapes(“Dashboard_Theme_4”).Visible = False

 

        ‘Color Theme Buttons Status

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_1”), True)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_2”), False)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_3”), False)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_4”), False)

   

    End With

 

End Sub

 

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

 

Sub Display_Theme_2()

 

    With ActiveSheet

 

        ‘Dashboard Themes Visibility

        .Shapes(“Dashboard_Theme_1”).Visible = False

        .Shapes(“Dashboard_Theme_2”).Visible = True

        .Shapes(“Dashboard_Theme_3”).Visible = False

        .Shapes(“Dashboard_Theme_4”).Visible = False

 

        ‘Color Theme Buttons Status

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_1”), False)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_2”), True)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_3”), False)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_4”), False)

   

    End With

 

End Sub

 

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

 

Sub Display_Theme_3()

 

    With ActiveSheet

 

        ‘Dashboard Themes Visibility

        .Shapes(“Dashboard_Theme_1”).Visible = False

        .Shapes(“Dashboard_Theme_2”).Visible = False

        .Shapes(“Dashboard_Theme_3”).Visible = True

        .Shapes(“Dashboard_Theme_4”).Visible = False

 

        ‘Color Theme Buttons Status

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_1”), False)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_2”), False)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_3”), True)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_4”), False)

 

    End With

 

End Sub

 

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

 

Sub Display_Theme_4()

 

    With ActiveSheet

 

        ‘Dashboard Themes Visibility

        .Shapes(“Dashboard_Theme_1”).Visible = False

        .Shapes(“Dashboard_Theme_2”).Visible = False

        .Shapes(“Dashboard_Theme_3”).Visible = False

        .Shapes(“Dashboard_Theme_4”).Visible = True

 

        ‘Color Theme Buttons Status

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_1”), False)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_2”), False)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_3”), False)

        Call Change_Radio_Button_Status(.Shapes(“Theme_Button_4”), True)

   

    End With

 

End Sub

VBA Code for Toggle Buttons (Elements Visibility Control)​

For the Toggle Buttons we also have to take a look at two different functional requirements:

  1. Buttons’ Visual State: We want the toggle buttons themselves to change their visual appearance correctly. That includes not only a change in colors, but also a change of position for the circle element (to the right for the active state/ to the left for the inactive state).
  2. Theme Colors: We want the dashboard elements (either info button or tab buttons plus content) to appear or disappear. Additionally, for both cases, we want the dashboard elements to reset into a default state when being hided and redisplayed.

The VBA code for the Toggle Buttons can then be realised as follows.

‘Toggle Buttons

 

Function Change_Toggle_Button_Status (Toggle_Circle As Object,Toggle_Background As Object,

Toggle_Textbox As Object, Active As Boolean)

 

    If Active = True Then

 

        With Toggle_Circle

            .Fill.ForeColor.RGB = RGB(0, 135, 65) ‘dark green

            .Left = Toggle_Circle.Left + 17 ‘move Toggle_Circle 17 pt to the right

        End With

 

        Toggle_Background.Fill.ForeColor.RGB = RGB(50, 180, 50) ‘light green

        Toggle_Textbox.TextFrame.Characters.Text = “On”

 

    Else

 

        With Toggle_Circle

            .Fill.ForeColor.RGB = RGB(255, 255, 255) ‘white

            .Left = Toggle_Circle.Left – 17 ‘move Toggle_Circle 17 pt to the left

        End With

 

        Toggle_Background.Fill.ForeColor.RGB = RGB(169, 169, 160) ‘light grey

        Toggle_Textbox.TextFrame.Characters.Text = “Off”

 

    End If

 

End Function

 

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

 

Sub Change_Info_Visibility()

 

    With ActiveSheet

       

        If .Shapes(“Toggle_Circle_Info”).Fill.ForeColor.RGB = RGB(255, 255, 255) Then

 

            ‘Visibility Info Elements

            With .Shapes(“Info_Button_Deliveries”)

                .Visible = True

                .Fill.ForeColor.RGB = RGB(255, 255, 255)

            End With

 

            .Shapes(“Info_Box_Deliveries”).Visible = False

 

            ‘Toggle Button Status

            Call Change_Toggle_Button_Status(.Shapes(“Toggle_Circle_Info”),

                                             .Shapes(“Toggle_Background_Info”),

                                             .Shapes(“Toggle_Textbox_Info”),

                                             True)

 

        Else

           

            ‘Visibility Info Elements

            .Shapes(“Info_Button_Deliveries”).Visible = False

            .Shapes(“Info_Box_Deliveries”).Visible = False

 

            ‘Toggle Button Status

            Call Change_Toggle_Button_Status(.Shapes(“Toggle_Circle_Info”),

                                             .Shapes(“Toggle_Background_Info”),                                              

                                             .Shapes(“Toggle_Textbox_Info”),

                                             False)

       

        End If

 

    End With

 

End Sub

 

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

 

Sub Change_Tabs_Visibility()

 

    ‘Rules

    ‘Visibility Off –> Reset/ Display Sales Revenue Charts

    ‘Visibility On –> Display Sales Revenue Charts by default

 

    With ActiveSheet

 

        If .Shapes(“Toggle_Circle_Tabs”).Fill.ForeColor.RGB = RGB(255, 255, 255) Then

 

            ‘Visibility of Tab Buttons

            .Shapes(“Tab_Button_Sales_Revenue”).Visible = True

            .Shapes(“Tab_Button_Sales_Units”).Visible = True

 

            With .Shapes(“Tab_Button_Sales_Revenue”)

                .TextFrame.Characters.Font.Color = RGB(0, 0, 0) ‘black

                .Fill.Transparency = 0#

            End With

 

            With .Shapes(“Tab_Button_Sales_Units”)

                .TextFrame.Characters.Font.Color = RGB(255, 255, 255) ‘white

                .Fill.Transparency = 1#

            End With

 

            ‘Visibility of Tab Content

            .Shapes(“Map_Chart_Sales_Revenue”).Visible = True

            .Shapes(“Line_Chart_Sales_Revenue”).Visible = True

            .Shapes(“Map_Chart_Sales_Units”).Visible = False

            .Shapes(“Line_Chart_Sales_Units”).Visible = False

 

            ‘Toggle Button Status

            Call Change_Toggle_Button_Status(.Shapes(“Toggle_Circle_Tabs”),

                                             .Shapes(“Toggle_Background_Tabs”),

                                             .Shapes(“Toggle_Textbox_Tabs”),

                                             True)

        Else

 

            ‘Visibility of Tab Buttons

            .Shapes(“Tab_Button_Sales_Revenue”).Visible = False

            .Shapes(“Tab_Button_Sales_Units”).Visible = False

 

            ‘Visibility of Tab Content

            .Shapes(“Map_Chart_Sales_Revenue”).Visible = True

            .Shapes(“Line_Chart_Sales_Revenue”).Visible = True

            .Shapes(“Map_Chart_Sales_Units”).Visible = False

            .Shapes(“Line_Chart_Sales_Units”).Visible = False

 

            ‘Toggle Button Status

            Call Change_Toggle_Button_Status(.Shapes(“Toggle_Circle_Tabs”),

                                             .Shapes(“Toggle_Background_Tabs”),

                                             .Shapes(“Toggle_Textbox_Tabs”),

                                             False)

 

        End If

 

    End With

 

End Sub

Conclusion

That’s already it for this seventh episode of the Ultimate Excel Dashboard tutorial series. We recommend to have a look at all the previous episodes if you haven’t already in order to build this dashboard from scratch. They are linked at the beginning of this post.

Also, 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