Excel: Dynamically change the chart calculation type based on the value of the selected clip element

advertisements

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.