I have a dashboard which tracks different financials for the company. One of the charts on this dashboard shows the annual totals for whatever value that the user selects in a slicer.
The problem that I'm encountering is the chart always shows the
sum of the values from each month, when sometimes
average would be more appropriate.
Is there a way to have the chart update the calculation type based on what slicer is selected?
Here is the code (not working properly) (credit to @ARich) :
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) On Error GoTo err1 Dim S As Slicer Dim sI As SlicerItem Dim PT As PivotTable ' Application.EnableEvents = False Set S = ThisWorkbook.SlicerCaches("Slicer_Type").Slicers("Type") For Each sI In S.SlicerCache.SlicerItems If sI.HasData And sI.Selected Then If sI.Caption = "Headcount" Then With Target.PivotFields("Sum of Value") .Caption = "Average of Value" .Function = xlAverage End With Exit For Else With Target.PivotFields("Average of Value") .Caption = "Sum of Value" .Function = xlSum End With Exit For End If End If Next err1: ' Application.EnableEvents = True End Sub
In short: no. What you are looking for is an event that fires when you change from one slicer to another. But there is no event in Excel that captures the
selection of a slicer or the
activation thereof or any other usable event.
Yet, if you are willing to create your own slicer with a hovering form (modeless) then you would be able to capture that event.