vba [SheetName][PivotTableName] 中已有数据”。要替换吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41638807/
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
There's already data in [SheetName][PivotTableName]". Do you want to replace it?
提问by zagasi
I am working with an Excel 2016 template with pivot tables, pivot charts, and a dashboard where I am dynamically updating data depending on various selections and filters. Whenever I open up a new version of the report (Excel file) I get the following message for each chart:
我正在使用带有数据透视表、数据透视图和仪表板的 Excel 2016 模板，我在其中根据各种选择和过滤器动态更新数据。每当我打开报告的新版本（Excel 文件）时，我都会收到每个图表的以下消息：
There's already data in [SheetName][PivotTableName]". Do you want to replace it?
[SheetName][PivotTableName] 中已有数据”。要替换吗？
I have browsed the internet and I have found 2 different solutions that have not worked.
我浏览了互联网，发现 2 种不同的解决方案没有用。
I tried to create a Macro that disabled messages and then refreshed all data. I used the following bit of VBA code to disables messages but it did not work as expected:
Applictaion.DisplayAlerts = falseI went into the advanced options of Excel I tried to disable the setting for "Alert before overwriting cells". This did not solve my issues and I asked a coworker to try on his machine as well and he too got the same message.
我试图创建一个禁用消息的宏，然后刷新所有数据。我使用了以下 VBA 代码来禁用消息，但它没有按预期工作：
Applictaion.DisplayAlerts = false我进入了 Excel 的高级选项，我试图禁用“覆盖单元格之前发出警报”的设置。这并没有解决我的问题，我让一位同事也在他的机器上试了一下，他也得到了同样的消息。
Does anyone know how to disable this message from popping up?
有谁知道如何禁用此消息弹出？
回答by Felipe Ribeiro
In addition for your first solution:
此外，对于您的第一个解决方案：
- Use before the code that pop's the warning:
- 在弹出警告的代码之前使用：
Application.DisplayAlerts = False
Application.DisplayAlerts = False
- After the code is finished, enable the alrert mode again:
- 代码完成后，再次启用alrert模式：
Application.DisplayAlerts = True
Application.DisplayAlerts = True
回答by Felipe Ribeiro
' There are anothers events that can be Disabled before de code and Enabled after the code to improve the performance of the vba.
' 还有一些事件可以在 de code 之前 Disabled 和 Enabled 在 code 之后，以提高 vba 的性能。
' 1. Before the code that pop's the warning, disable all:
' 1.在弹出警告的代码之前，禁用所有：
Sub DisableAll()
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:="your password"
Next
Exit Sub
End Sub
' 2. After the code is finished, enable all events, alerts and protected sheets again:
' 2. 代码完成后，再次启用所有事件、警报和受保护的工作表：
Sub EnableAll()
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Protect Password:="your password", DrawingObjects:=True, contents:=True, Scenarios:=True _
, UserInterfaceOnly:=True, AllowFiltering:=True
Next
Exit Sub
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
