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

Question

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?

Solution 1

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.

Solution 2

Before your selection put

Application.Calculation = xlmanual 

After your selection

Application.Calculation = xlautomatic

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