Start a macro when changing a value for Slicer

[jack10063]

Hi,

I'd like to start a macro every time a value is changed in a Slicer. Any ideas how to do this??

Br,
Jack

 

[SteveO59L]

Whats a slicer ?

 

[RoryA]

I don't think you can specifically. If you assign a macro to the slicer, it stops working as a slicer, and there are no events associated with slicers. Nearest thing I can think of would be the worksheet's PivotTableUpdate event and store the old value of the relevant field in a variable which you check each time the table updates.

 

[RoryA]

Whats a slicer ?

It's a mechanism for filtering pivot tables in 2010.

 

[jack10063]

Rorya,

Thanks for your answer. I am trying to use PivotTableUpdate as you suggest:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
If ActiveSlicer.Name = "slicer_1" Then
 '   MsgBox "test"
End If
 
End Sub

So, I would like to start the macro only if the active Slicer is "slicer_1". Unfortunately, I am getting a compile error saying: "argument not optional". Thus, it seems that I cannot use "ActiveSlicer.Name". How could I get the name for the active Slicer??

Br,
Jack

 

[OPERO]

Hi,

I'd like to start a macro every time a value is changed in a Slicer. Any ideas how to do this??

Br,
Jack

This works for me, mileage may vary. This depends on the fact that a slicer operates on one or more pivottables, and you can trap changes to ANY pivottable using Workbook_SheetPivotTableChangeSync.

Add a macro like this:

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

changeForecastHeader is my macro to change my chart header to insert the slicer details, replace this with a call to your own macro.

 

[Shahad Najrath]

Hi Guys,

I have applied a slicer to a data table, and i dont want user to "clear filters" on the slicers.
The user can only able to use the buttons in the slicers, but the "clear filters" should be disabled, please help me how it can be done by any way.

 

[Whb23]

I found a Workaround for this issue

1- Create an ID for the Table, the ID should be calculated automatically (something like =ROW(Any column in the table)
2- With another sheet or the same sheet use SMALL Function with SUBTOTAL function to extract the ID of the filter. Subtotal Let you work only with the filtered values, in your case only one value.
3- Create a Worksheet_Calculate() event to track when the cell with formula change and add the macro there.

Here is a short video showing how I did it.

I hope this helps
https://www.dropbox.com/s/gx4wqkqcnp6eim9/Slicer Macro Demo.webm?dl=0