Update a pivot chart from a slicer change to a table

Hi. I've got a data table (Table1) and a slicer. On another sheet, I have a pivot table and chart. I'd like to be able to trigger the pivot chart update when the slicer gets changed. I've tried the code below (which works as a standalone module) in a worksheet_change event, and that didn't work. From reviewing a plethora of posts, it appears that a slicer change can't trigger this event. So I tried adding a sub-total formula to cell $Z$1 on the worksheet and tried the Worksheet_SelectionChange event, and that also didn't work. I can sort of get my head around why the first one didn't work, but why wouldn't the Worksheet_SelectionChange event see the change in cell Z1? Is there any way to get this to trigger the event? Thanks for looking.

    
    Dim wkb As Workbook
    Dim wksData As Worksheet
    Dim wksPT As Worksheet
    Dim cCountry As String
    Dim FirstVizCell As String
    Dim pt As PivotTable
    Dim Field As PivotField

    Set wkb = ThisWorkbook
    Set wksData = wkb.Sheets("OUS Complaints")
    Set wksPT = wkb.Sheets("Escalation Pivot Tables & Chart")

    Set pt = wksPT.PivotTables("PivotTable1")
    Set Field = pt.PivotFields("Country")
    
    If Target.Address = "$Z$1" Then
    
        wkb.Activate
        wksData.Select
        With wksData
            FirstVizCell = Range("D3:D" & Rows.Count).SpecialCells(xlVisible)(1).Address
            cCountry = Range(FirstVizCell).Value
            'This updates and refreshes the PIVOT table
            With pt
                Field.ClearAllFilters
                Field.CurrentPage = cCountry
                pt.RefreshTable
            End With
        End With

    End If

End Sub

have you tried saving the workbook and reopening it? Sometimes the workbook/worksheet triggers do not load after they have been changed unless they are present when the workbook first opens.

Yes, I tried that. Both events trigger if I update cell Z1 manually, but not if it gets updated via the Slicer change.

Try to use the following trigger. I am not familiar enough with pivot charts to say if it works exactly like I think it should, but I think this is the trigger that is fired when a pivot chart/slicer is changed or updated.

Code pulled from here.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

If ActiveWorkbook.SlicerCaches("Slicer_Region").SlicerItems("East").Selected = True Then
UserForm1.Show
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Region").SlicerItems("West").Selected = True Then
UserForm2.Show
End If

End Sub

If you are wanting the macro to fire with the slicer, you could do a workaround that says when the slider is moved have Z1 become selected. Once Z1 is selected, your chart should update, then select the previous selected cell. If you contain this inside of a Application.ScreenUpdating = False then the user will never see it.

However, there are other declarations I found, just looking around inside of the Worksheet VBA section. You could try your hand with some of these and see what happens when?

Private Sub Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)

End Sub

Private Sub Workbook_SheetPivotTableAfterValueChange(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)

End Sub

Private Sub Workbook_SheetPivotTableBeforeAllocateChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

End Sub

Private Sub Workbook_SheetPivotTableBeforeCommitChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

End Sub

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)

End Sub

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)

End Sub

After sleeping on it I think I understand now. You want the chart to update when the value changes in cell Z1, correct?

Currently, your code only checks if the cell Z1 is selected, THEN it updates the cell. Selecting Z1 and changing the value are two different things.

To check to see if a value has changed, you can set the value to a public string. If that string matches the previous string, then do nothing. If it does not, then update.

Here is an example:

Public PreviousVal As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    

    Dim wkb As Workbook
    Dim wksData As Worksheet
    Dim wksPT As Worksheet
    Dim cCountry As String
    Dim FirstVizCell As String
    Dim pt As PivotTable
    Dim Field As PivotField

    Set wkb = ThisWorkbook
    Set wksData = wkb.Sheets(1)
    Set wksPT = wkb.Sheets(2)

    'Set pt = wksPT.PivotTables("PivotTable1")
    'Set Field = pt.PivotFields("Country")
    
    'If Target.Address = "$Z$1" Then
    
    If PreviousVal = "" Or PreviousVal <> Target.Value Then
    
    
        wkb.Activate
        wksData.Select
        With wksData
            FirstVizCell = Range("D3:D" & Rows.Count).SpecialCells(xlVisible)(1).Address
            cCountry = Range(FirstVizCell).Value
            'This updates and refreshes the PIVOT table
'            With pt
'                Field.ClearAllFilters
'                Field.CurrentPage = cCountry
'                pt.RefreshTable
'            End With
        End With
    Else
        
    End If
    
End Sub

Hi. Thanks for looking at this. I tried what you suggested, and it's still not triggering the event when I change the Slicer. I did sort of a work around that isn't ideal, but it works. In the email sub, I added the code below. Since that's in a regular sub and actually changes the value, it triggers the worksheet_change event, when changes the pivot table/chart. Thank you for taking the time to help!

myZvalue = Range("Z1").Value
If myZvalue = 1 Then
Range("Z1").Value = 2
Else
If myZvalue = 2 Then
Range("Z1").Value = 1
End If
End If