Excel VBA - Selecting multiple slicer items at once without a refresh

2
votes

I'm trying to select and deselect multiple slicer items from a single slicer without having it trigger an update until all my selections are complete. I'm able to do this pretty simply in the Excel front-end by Ctrl-clicking all the selections I want. However, when I record a macro with this behavior and run it, it updates after each selection/deselection (the recorded macro is just a bunch of .Selection = True/False statements within a With block).

I've tried using the SlicerCaches.VisibleSlicerItemsList function, but that throws a 1004 Application error - even when I've used the SlicerItem.Name field to populate the array: 

Dim tntw(0 To 2) as Variant
For i = 0 To 2
    tntw(i) = sc.SlicerItems(i + 1).Name
Next i
sc.VisibleSlicerItemsList = tntw

I've also tried setting all dependent PivotTables to manual update for this, as well as trying to set the application.calculation to manual (and switching both back at the end), but neither accomplish what I'm looking for.

Any ideas?

2
excelpivot-tablevba
Have you tried Application.EnableEvents = False before executing the code and then resetting it after?? – Joseph
That looks to do it - Thanks! As a note, I need to turn events back on just prior to my last selection so other events I need to capture actually fire, but this seems to do it. – Kevin Pope

2 Answers

1
votes

Before your selection put 

Application.Calculation = xlmanual

After your selection

Application.Calculation = xlautomatic

This worked perfectly for me. I had the exact same problem.

1
votes

As @joseph4tw posted in the comments, all that is really needed is the Application.EnableEvents = False line. However, I needed to re-enable events again before the final Slicer was iterated to make the event actually fire. I don't have access to the code any longer (previous job) but imagine the solution involved counting the number of Slicers and at n-1 in the loop call to re-enable events.