Excel VBA - 一次选择多个切片器项目而不刷新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13960407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Selecting multiple slicer items at once without a refresh
提问by Kevin Pope
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/Falsestatements within a Withblock).
我正在尝试从单个切片器中选择和取消选择多个切片器项目，而不会触发更新，直到我的所有选择都完成。通过按住 Ctrl 键单击我想要的所有选择，我可以在 Excel 前端非常简单地完成此操作。但是，当我记录具有此行为的宏并运行它时，它会在每次选择/取消选择后更新（记录的宏只是块中的一堆.Selection = True/False语句With）。
I've tried using the SlicerCaches.VisibleSlicerItemsListfunction, but that throws a 1004 Application error - even when I've used the SlicerItem.Namefield to populate the array:
我已尝试使用该SlicerCaches.VisibleSlicerItemsList函数，但会引发 1004 Application 错误 - 即使我已使用该SlicerItem.Name字段填充数组：
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.
我还尝试将所有相关的数据透视表设置为手动更新，并尝试将 application.calculation 设置为手动（并在最后切换回），但都没有完成我正在寻找的内容。
Any ideas?
有任何想法吗？
采纳答案by Kevin Pope
As @joseph4tw posted in the comments, all that is really needed is the Application.EnableEvents = Falseline. 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.
正如@joseph4tw 在评论中发布的那样，真正需要的只是这Application.EnableEvents = False条线。但是，在迭代最终的 Slicer 以使事件真正触发之前，我需要再次重新启用事件。我无法再访问代码（以前的工作），但想象一下解决方案涉及计算切片器的数量并在循环调用中的 n-1 处重新启用事件。
回答by ScholarYoshi
Before your selection put
在您选择之前放
Application.Calculation = xlmanual
After your selection
选择后
Application.Calculation = xlautomatic
This worked perfectly for me. I had the exact same problem.
这对我来说非常有效。我有同样的问题。
