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.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call RunMacroOnSlicerChange
End Sub
Sub RunMacroOnSlicerChange()
Dim sC As SlicerCache
Dim sL As SlicerCacheLevel
Dim i As Long
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Month")
If sC.SlicerItems.Count = sC.VisibleSlicerItems.Count Then ' if none of the slicer items are selecting in Slicer_Month
'Do nothing
Else ' If at least 1 slicer item is selected from Slicer_Month
'MsgBox "hello"
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Month"), ColumnFields:=Array("Product Name"), PageFields:=Array("Product Category")
End If
End Sub
Bookmarks