How to Change Pivot Chart Axes Based on Slicer Selection via VBA
Question details
The user needs a VBA macro to detect a product slicer selection and automatically update the minimum and maximum values for both the primary and secondary axes of a PivotChart.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Filtering a complex PivotChart using a slicer, where each selected product requires distinctly different axis scales to display the data correctly.
- Observed behavior
- The chart axes remain static or use auto-scaling that does not fit the specific minimum and maximum limits required for the dynamically selected item.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings so the VBA event handlers can execute.
Write a Macro Using the Worksheet_PivotTableUpdate Event
Create an event-driven VBA script that triggers whenever the slicer updates the PivotTable, dynamically adjusting the specific chart axes.
Because slicers natively filter the underlying PivotTable, you cannot attach a macro directly to a slicer click. Instead, you must use the Worksheet_PivotTableUpdate event. This event fires whenever the PivotTable data changes, allowing your code to check the active slicer item and apply the necessary minimum and maximum scales to the corresponding PivotChart axes.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left, double-click the specific worksheet (e.g., Sheet1) that contains your PivotTable and Slicer. Do not place this code in a standard module.
From the left drop-down menu at the top of the code window, select 'Worksheet'. From the right drop-down menu, select 'PivotTableUpdate'. This creates the Private Sub Worksheet_PivotTableUpdate shell.
Write code to loop through the 'SlicerCaches' object (e.g., ActiveWorkbook.SlicerCaches("Slicer_Product")) to determine which 'SlicerItem' has its 'Selected' property set to True.
Use a Select Case statement based on the selected product to assign new values to the chart. Target the primary axis using ActiveSheet.ChartObjects("YourChartName").Chart.Axes(xlValue, xlPrimary).MinimumScale (and MaximumScale), and repeat for xlSecondary.
Try WPS Office for Seamless Spreadsheet Management
While highly complex VBA slicer event scripting relies heavily on the Microsoft Excel object model, WPS Office provides a robust, lightweight, and free alternative for your comprehensive spreadsheet, charting, and data analysis needs. It delivers excellent format compatibility and a familiar interface without the high subscription costs.
- 1. Download and Install: Visit the official WPS website to download the free installation package and follow the on-screen prompts.
- 2. Open Your Workbooks: Launch WPS Spreadsheets and open your existing Excel workbooks to instantly access your PivotTables and charts.
- 3. Analyze Data Seamlessly: Utilize built-in slicers and advanced charting tools to handle your data visualization needs intuitively.
Frequently Asked Questions
Can I change PivotChart axes based on a slicer without using VBA?
No, spreadsheet software does not currently have a built-in feature to dynamically link chart axis scale limits (minimum/maximum) directly to a slicer selection. The axes will only auto-scale based on the visible data, which may not always fit exact custom requirements unless automated with a VBA macro.
How do I find the exact name of my slicer cache for the VBA code?
Right-click the slicer in your worksheet and select 'Slicer Settings'. Look at the 'Name to use in formulas' field at the top of the dialog box. This is your Slicer Cache name, which typically begins with 'Slicer_' followed by the field name.
Why doesn't my VBA macro trigger when I click the slicer?
Slicer clicks do not have their own direct VBA click events. Ensure your code is placed inside the specific Worksheet object module using the `Worksheet_PivotTableUpdate` event rather than a standard module, and verify that macros are fully enabled in your software's Trust Center.
