Power BI VBA

Custom Toggle Button in Excel and Power BI

Table view in Excel

In this article, we have created a custom toggle button in Microsoft Excel and Microsoft Power BI to toggle the Chart and Table on click of this toggle button. This trick could be useful in a dashboard wherein you have less space.

Watch the step by step video tutorial:

Custom Toggle Button in Microsoft Excel

We have used 2 images to create the toggle button in Excel. We have created the 2 macros to show the chart and Table and assigned the macros to these buttons.

Below are the images of the Toggle buttons. We will arrange them on top of each other.

Toggle Button
Toggle Button

We have created below given macros and assigned to the respective buttons-

Sub Show_Table()

Dim sh As Worksheet
Set sh = ActiveSheet


sh.Shapes("Btn_Table").Visible = msoFalse
sh.Shapes("Btn_Chart").Visible = msoCTrue
sh.Shapes("Chart").Visible = msoFalse
sh.Range("A:N").EntireColumn.Hidden = False

End Sub


Sub Show_Chart()

Dim sh As Worksheet
Set sh = ActiveSheet

sh.Shapes("Btn_Table").Visible = msoCTrue
sh.Shapes("Btn_Chart").Visible = msoFalse
sh.Shapes("Chart").Visible = msoCTrue

sh.Range("A:N").EntireColumn.Hidden = True

End Sub

If we will click on the Chart button then it will display chart view-

Chart View in Excel
Chart View in Excel

 

If will click on the Table button then it will display the Table view-

Table view in Excel
Table view in Excel

 

Custom Toggle Button in Microsoft Power BI

In the Power BI, we can create the Bookmarks to create the toggle buttons. First, import the toggle button images form Insert >> Image and create the chart and tables as per your requirements.

Import Images in Power BI
Import Images in Power BI

 

Open the selection pane from View >> Selection. To create the Table view bookmarks, hide the table button, and chart form selection pane.

Open the Bookmarks pane from View >> Bookmarks. Add a bookmark and rename with Table_Name.

Selection Pane and Bookmark
Selection Pane and Bookmark

Now hide the chart button and matrix and show the table button and chart from the selection pane. Add a bookmark and rename it with Chart_View.

Selection Pane and Bookmark
Selection Pane and Bookmark

Select the table button in the selection pane and add an action as a bookmark. Select the Table_Veiw bookmark.

Assign bookmark on table button
Assign bookmark on table button

Now select the chart button in the selection pane and add an action as a bookmark. Select the Chart_Veiw bookmark.

Assign bookmark on chart button
Assign bookmark on chart button

 

Now you can keep both buttons on top of each other. Now, these buttons will work like a toggle button to show the Chart and Table.

See the below live Power BI report

Click here to download the practice files.

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com