Preventing a Recursive Call of "SheetPivotTableUpdate"

[peglegpete]

I have a pivot table hooked to an external data source.

I need to set the pivot's filter settings automatically for this pivot table whenever it is refreshed. See the attached image if you aren't sure what I mean by that.

So I recorded a macro while doing this, and got the .Visible property of the PivotItems object. This, in theory, works but the problem is when I use that code in the SheetPivotTableUpdate command, it becomes recursive (infinite loop) because changing this property fires the exact same event.

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
MsgBox ("starting custom pivot table code")
Application.ScreenUpdating = False

With Target.PivotFields("OneOfTheFields")
    .PivotItems("blahblah").Visible = False
    .PivotItems("yadayada").Visible = False
End With

Application.ScreenUpdating = True
End Sub

My event handling knowledge isn't so hot, but I feel like there is a way to prohibit it from firing again.

My bottomline question is does anyone know how to set the filter settings automatically in a pivottable after it has been refreshed?


Thank you for reading. I really appreciate any and all replies.

also posted on http://www.xtremevbtalk.com/showthread.php?p=1323740

 

[Richard Schollar]

Hi

You should be able to use the Application.EnableEvents setting to control this:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
MsgBox ("starting custom pivot table code")
Application.ScreenUpdating = False
Application.EnableEvents = False
With Target.PivotFields("OneOfTheFields")
.PivotItems("blahblah").Visible = False
.PivotItems("yadayada").Visible = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[peglegpete]

Ah!! I had a feeling something like that existed.... thank you so much. I'll try that right now.