I have a pivot table + pivot chart with 3 slicers for fiscal years, quarters and months. All slicers/slicers items are by default unselected. What I would like to do is when the user make at least one selection of a slicer item in the month slicer (for example January), it would trigger an event to run a macro to change the pivot table row field to month instead of the original row field of fiscal year + quarter. If the user makes a change in the slicer for fiscal years or quarters, nothing needs to happen, the pivot table row field stay the same (fiscal year + quarter).
I have tested my macro initially with just a message box appearing and it is working as I want it to. My problem comes when I add the code to modify the pivot table to the change the pivot table row field to month. Because I have my trigger event on any pivot table change, when the macro make a change to the pivot table row field, it relaunch the macro (RunMacroOnSlicerChange) indefinitely and it never get to end sub and I get the following error message: Run-time error ‘-2146417949 (800010108)’: Automation Error: Object invoked has disconnected from its Clients.
Question: Can I use another event trigger other than PivotTableUpdate? From what I found on the web, there is no such thing as a slicer selection event trigger. Or is there a way that I can temporarily unable the PivotTableUpdate so my macro can complete and then reenable it after? Thank you for your time, below is my VBA code.
P.S. I am using Excel 2010.
Bookmarks