Excel VBA-一次选择多个切片器项目而无需刷新 [英] Excel VBA - Selecting multiple slicer items at once without a refresh

问题描述

我正在尝试从单个切片器中选择和取消选择多个切片器项目，而不会触发更新，直到所有选择都完成为止.通过按住Ctrl键并单击我想要的所有选择，我可以非常简单地在Excel前端中执行此操作.但是，当我记录具有这种行为的宏并运行它时，它在每次选择/取消选择后都会更新(记录的宏只是With块中的一堆.Selection = True/False语句).

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).

我尝试使用SlicerCaches.VisibleSlicerItemsList函数，但是会引发1004应用程序错误-即使当我使用SlicerItem.Name字段填充数组时:

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

我还尝试为此设置所有相关的数据透视表以进行手动更新，并尝试将application.calculation设置为手动(并在最后将两者都切换回去)，但都没有完成我想要的操作.

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.

有什么想法吗?

推荐答案

正如@ joseph4tw在评论中发布的，真正需要的只是Application.EnableEvents = False行.但是，我需要在迭代最终的Slicer之前再次重新启用事件，以使事件真正触发.我再也无法访问代码了(以前的工作)，但可以想象解决方案涉及计算切片器的数量，并在循环调用中以n-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.

这篇关于Excel VBA-一次选择多个切片器项目而无需刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！