Pivot Table Issues

How to Use VBA Buttons to Switch PivotTables and Slicers in Excel

Aamir Naveed Akram Oct 22, 2026 868 views

Question details

The user wants to use VBA buttons on a dashboard to switch between different PivotTables and slicers (e.g., for different product categories) in the same location without creating multiple worksheets.

How to Use VBA Buttons to Switch PivotTables and Slicers on an Excel Dashboard

Product: Excel
Device & OS: not provided
Scenario: Creating an interactive financial or product dashboard where major product categories like electronics and appliances have their own income and expense data.
Observed behavior: Dynamically changing the PivotTable source, applying filters, and refreshing the view in a single dashboard area using macro-assigned buttons.

Before you start

Before proceeding, ensure that the Developer tab is visible in your ribbon and that you have enabled macros in your Trust Center settings so your VBA scripts can run smoothly.

Solution 1Recommended

Use VBA and Select Case to Switch PivotTable Data

This solution involves creating a base PivotTable, adding shapes as buttons, and using a VBA macro with a Select Case statement to update the data source and refresh the slicers dynamically.

By utilizing a single PivotTable as your dashboard's focal point, you avoid the clutter of multiple sheets. The VBA macro will act as a toggle, changing the underlying filter or data source based on which button you click.

Create the Base PivotTable and Slicers

Highlight your initial dataset, navigate to Insert > PivotTable, and place it on your Dashboard sheet. Then, go to PivotTable Analyze > Insert Slicer to add your required slicers (e.g., Product Category).

Insert Dashboard Buttons

Go to Insert > Shapes and choose a shape (like a rounded rectangle) to act as your button. Draw it on the dashboard, right-click to Edit Text, and label it with your product category (e.g., 'Electronics'). Repeat for other categories.

Write the VBA Macro

Press ALT + F11 to open the VBA Editor. Go to Insert > Module and write a script using a 'Select Case' statement that changes the currentProduct variable, updates the PivotTable's PivotFields based on the selected category, and calls ActiveSheet.PivotTables("YourPivotTable").PivotCache.Refresh.

Assign the Macro to the Buttons

Close the VBA Editor. Right-click your newly created shape on the dashboard, select 'Assign Macro...', choose the specific macro script you just wrote for that product category, and click OK.

Use VBA and Select Case to Switch PivotTable Data

Save as Macro-Enabled: Remember to save your workbook as an Excel Macro-Enabled Workbook (*.xlsm) so your VBA buttons remain functional the next time you open the file.

Advanced Data Analysis

Build Interactive Dashboards with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, PivotTables, and Slicers, allowing you to create powerful, interactive dashboards just like in Microsoft Excel. It offers seamless compatibility for complex macro-enabled files.

1. Open your Dashboard File: Launch WPS Spreadsheet and open your dataset or existing dashboard.
2. Insert PivotTables and Slicers: Navigate to the Insert tab to add your PivotTable, then use the PivotTable Tools to insert Slicers for your data categories.
3. Access the VBA Editor: Go to the Developer tab (enable it in settings if hidden) and click on 'Macros' or 'Visual Basic' to script your Select Case logic.
4. Assign Macros to Shapes: Insert shapes from the Insert tab, right-click, and select 'Assign Macro' to link your automation script to the button.

Fully compatible with Microsoft Excel (.xlsx and .xlsm formats)Built-in VBA macro support for advanced dashboard automationComprehensive PivotTable and Slicer featuresLightweight, fast, and free to use

microsoft office alternative - wps office

Frequently Asked Questions

Can I connect multiple PivotTables to a single slicer in my dashboard?

Yes. Right-click the slicer, select 'Report Connections' (or 'PivotTable Connections'), and check the boxes next to all the PivotTables you want to control with that specific slicer.

Why is my VBA macro not running when I click the shape?

Ensure that macros are enabled in your Trust Center settings. Additionally, verify that you have properly assigned the macro by right-clicking the shape and selecting 'Assign Macro' to confirm the correct script is linked.

Can VBA be used to hide or show specific slicers based on the button clicked?

Yes, you can use VBA to toggle the visibility of a slicer by referencing its shape object and setting its '.Visible' property to True or False within your Select Case statement.

Do I need to create separate macros for every single button?

No. You can write a single macro that determines which button called it (using Application.Caller) and uses a Select Case statement to apply the appropriate PivotTable filters based on the button's name.

Excel & Data Problems