1

Is there a way I can run a macro every time a slicer is selected? Currently I"m using a button to run the macro. I found forums that discuss running the macro on table changes or updates but since the data in the table is actually not changing they do not work. The following is what I've already tried

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim n As Integer
n = Application.WorksheetFunction.CountIf(Range("tbl_clients1[visible]"), 
"1")
If (n) = 1 Then
Sheets("clientlist").CommandButton2.Visible = True
Else
Sheets("clientlist").CommandButton2.Visible = False
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim n As Integer
n = Application.WorksheetFunction.CountIf(Range("tbl_clients1[visible]"), 
"1")
If (n) = 1 Then
Sheets("clientlist").CommandButton2.Visible = True
Else
Sheets("clientlist").CommandButton2.Visible = False
End If
End Sub
Community
  • 1
  • 1
Jeremy L
  • 11
  • 2
  • 5
  • One intermediate approach to think about https://stackoverflow.com/questions/33081249/detect-autofilter-changes?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa or https://stackoverflow.com/questions/31945512/how-to-trigger-an-update-after-a-non-pivottable-table-linked-slicer-update-occ?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – QHarr Apr 19 '18 at 18:59

1 Answers1

1

I do something similar to this in one of my projects. Here's my approach.

Because Table slicers don't have an associated event handler, I make a PivotTable out of the Table, put only the field of interest in it and hide it somewhere (usually another sheet), add a PivotTable slicer and put it by the Table you want to filter, and then capture the PivotTable_Update event that gets triggered as a result. I then iterate the visible items of the PivotTable slicer, and use that to set the filter in a hidden Table slicer, and also to trigger the macro I want to run. Works great.

This is similar to my answer at How to run a macro when a Filter is applied on Particular Pivot Field

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27