vba 如何禁止任何数据透视表更新的 Excel 消息？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21834775/
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
How do I suppress Excel message for any pivot table update?
提问by tlewis3348
I have created a pivot table to summarize some information and have added some formulas adjacent to the pivot table to do calculations on the numbers included in it. I have created a macro that re-enters the formulas whenever the user changes the size of the pivot table (in the PivotTableUpdate event) by showing or hiding various rows/columns of data.
我创建了一个数据透视表来总结一些信息，并在数据透视表旁边添加了一些公式来计算其中包含的数字。我创建了一个宏，每当用户通过显示或隐藏数据的各个行/列来更改数据透视表的大小（在 PivotTableUpdate 事件中）时，该宏都会重新输入公式。
My problem is that whenever columns of data are added to the pivot table, it asks me "Do you want to replace the contents of the destination cells?" I always click yes, because although the cells will be overwritten when the pivot table expands, the formulas will be re-entered in their correct cell and everything is fixed and formatted properly by the macro.
我的问题是，每当将数据列添加到数据透视表时，它都会询问我“您想替换目标单元格的内容吗？” 我总是单击是，因为尽管在数据透视表展开时单元格将被覆盖，但公式将重新输入到正确的单元格中，并且所有内容都由宏固定并正确格式化。
Therefore, I would like to know where I should put application.displayalerts = falseso that it is effective to suppress the message box whenever the user expands the pivot table.
因此，我想知道我应该放在哪里，application.displayalerts = false以便在用户展开数据透视表时有效地抑制消息框。
回答by Tuckshop86
Hi tlewis3348 i think this is what you are looking for
嗨 tlewis3348 我想这就是你要找的
Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub
回答by John Carey
FWIW This solution worked great for me. I just activated each sheet individually and called the UpdateIt function before doing anything else, e.g.,
FWIW 这个解决方案对我很有用。我只是单独激活每个工作表并在执行其他任何操作之前调用 UpdateIt 函数，例如，
Worksheets("RFS Monthly Summary").Activate
UpdateIt
Worksheets("RFS Daily Activity").Activate
UpdateIt
