Trigger event on slicer selection update?

[Stormin']

I have a pivot table set up with a few slicers, in a parent/child setup.
e.g. for cars, the "Brand" would be the parent and the "Model" will be the child.

The user selects the brand from Slicer_Brand, and then selects the model from Slicer_Model.

My aim is to clear the filter on the child slicer when the selection in the parent slicer is changed.

e.g. User selects "McLaren" from Slicer_Brand and then "P1" from Slicer_Model. Data is shown.
The user then selects "Ferrari" from Slicer_Brand. Data is rightly not shown since the combination "Ferrari P1" does not exist. This is where I would like to reset the filter on Slicer_Model so that all models are selected, ready for reselection by the user.

I have a line of code that clears the filter [ThisWorkbook.SlicerCaches("Slicer_Model").ClearManualFilter] but I don't know how to trigger it on the event that Slicer_Brand changes selection.

Does anyone have any ideas? Thanks.

[CK76]

Unfortunately there is no event that traps Slicer selection change.

What you can do is associate the code to Worksheet_PivotTableUpdate event.

[Stormin']

I have tried your suggestion but unfortunately it also triggers when you change the child slicer.

[CK76]

Hmm... can you upload small sample file (with dummy or sanitized data)?

You'd need some sort of check to exit sub, but not sure what condition to use without looking at your set up.

[xlnitwit]

Hi,

You might set up one or more additional pivot tables that only use the child slicer fields, and then monitor that/those for changes.

[Stormin']

Hi,

You might set up one or more additional pivot tables that only use the child slicer fields, and then monitor that/those for changes.

That is an interesting idea, I can see how that would work.


However, I have solved the problem in another fashion with the utilisation a UDF used elsewhere in the workbook that returns all the selected slicer items as a CSV string.
Credit for that particular UDF is http://www.jkp-ads.com/articles/slicers05.asp although my version is slightly modified.

Here is the code for both my PivotTableUpdate event and the UDF it uses:
(note that I have two parent slicers)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hopefully by posting my solution this will help someone somewhere at somepoint.