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
- 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
- create interactive dashboard tabs
- 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:
- Buttons’ Visual State: We want the radio buttons themselves to change their visual appearance (as a group) correctly.
- 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:
- 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).
- 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.
