Start a macro when changing a value for Slicer

jack10063

Board Regular
Joined
Aug 12, 2008
Messages
103
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

jack10063

Board Regular
Joined
Aug 12, 2008
Messages
103
Rorya,

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

Code:
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

New Member
Joined
Mar 6, 2013
Messages
1
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:

Code:
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

New Member
Joined
Apr 1, 2016
Messages
1
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

New Member
Joined
Mar 4, 2019
Messages
1
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
 

Forum statistics

Threads
1,078,236
Messages
5,339,010
Members
399,273
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top