Trapping Excel VBA Slicer click events

A similar unanswered question lies here: Excel Macro slicer onclick event

I have a ListObject and a Slicer on that ListObject. The slicer is called "Year". I'm able to trap the on click event with

Private Sub Year_Click()
   doCalcsOnFilteredListObject
End Sub

Just right click on the slicer heading > Add Macro

Problem is that I want the trapped event to happen, after the filter selection has been applied. Right now, I trap the event, the filter is never applied, but the calc function runs fine on the (unaltered) listobject.

There are no events for ListObjects and Slicers. The Year_Click you have is an event of container object not of SlicerItem.

The workaround is creating a DUMMY worksheet that has SUBTOTAL Formula on A1 Cell and trap a SheetCalculate event on it. So that when you click on Slicer, it recalculates the formula on DUMMY!A1 after filtering.

Procedures:

1. Create a new sheet named "DUMMY" & put a formula in A1 cell: "=SUBTOTAL(109, Table1[YEAR])". (Adjust appropriately to your ListObject & Column names.)

2. Put this VBA procedure.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  If Sh.Name = "DUMMY" Then
    doCalcsOnFilteredListObject
  End If
End Sub

3. Select criteria in Slicer.

This should trigger the desired event and call your custom procedure.

Be aware that Automatic Calculation must be turned on. If you want Manual Calculation, there is a technique using Workbook_Open that sets all sheets into Manual Calculation except the DUMMY sheet.