0

I am building a dashboard in which 3 sliders control 10 pivot tables on a second data sheet. I also have a map drawing on the pivot data. This is populated by a macro that needs to be triggered each time a selection is made on the slicer. After reading through old questions and other forums, I've used the following code, placed in the sheet that contains the pivots.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Application.EnableEvents = False

Call UpdateMaps

Application.EnableEvents = True

End Sub

With this, changes to the sliders do activate the macro to update the map, but despite the EnableEvents toggling, it triggers 10 times; once for each of the pivot tables, and so is very slow. Am I missing something? Is there a way to have only one triggering of the macro, which would take place after all of the pivot tables have finished updating from the Slicer selections?

Improve this question

1 Answer 1

Reset to default
0

You can limit it to running when a particular PivotTable has been updated like so: 

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Target.Name = "PivotTable1" then  UpdateMaps
End Sub

Don't bother turning off events. Change "PivotTable1" to the name of one of your PivotTables (unless you already have one called "PivotTable1" in your workbook)

Improve this answer
3
  • Thanks Jeffrey, appreciated. How does the timing of these events relate to each other? Will the macro run after all the pivot tables have updated, or will the timing of the macro depend on the pivot table I select, and where in the order of updating that pivot table sits? I ask because the macro pulls data from the pivot tables, so needs to run after they've all updated.
    – Will
    Commented Oct 18, 2017 at 4:34
  • Okay. In that case, you can use the application.ontime method to run the macro a predetermined time after the event handler is triggered, like this: If Target.Name = "PivotTable1" then application.ontime Now + TimeValue("00:00:15"), "UpdateMaps". (That runs the macro in 15 seconds after the slicer, and obvously you can change this to be as short as one second by changing the 15 to 01).
    – jeffreyweir
    Commented Oct 18, 2017 at 9:27
  • Alternately you could use a slicer that's connected to just one PivotTable that's hidden from the user, and which only contains the field of interest. Once they do that, it only generates one PivotTable_Update event, and you can easily determine which item they clicked on (because it will be the only one that's visible in the PivotTable). So you can use that PivotTable_Update event to reconnect that Slicer to the other Pivots (which forces them to filter the Pivot based on the selected item) then run your code, then disconnect the Slicer from all but one PivotTable again. Tricky, but works!
    – jeffreyweir
    Commented Oct 18, 2017 at 9:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .