vba 删除表格并避免 Excel 要求用户确认，而是使用自定义消息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8923694/
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
Delete Sheets and avoid Excel asking the user to confirm, using custom messages instead
提问by BuZz
I have a button that triggers a chain of events. One of these events is to delete a sheet. Before the user deletes anything, I pop up my custom YES/NO message asking them to confirm the whole process.
我有一个触发一系列事件的按钮。这些事件之一是删除工作表。在用户删除任何内容之前，我会弹出我的自定义 YES/NO 消息，要求他们确认整个过程。
Then comes the sub event of deleting the sheet, and Excel pops up its own windowfor confirming the removal of the sheet. Problem is that if the user says "no" at that point, that sets my application in an inconsistent state.
然后是删除工作表的子事件，Excel弹出自己的窗口，确认删除工作表。问题是，如果用户当时说“不”，那会使我的应用程序处于不一致的状态。
How can I bypass Excel asking to confirm the deletionof a sheet ?
如何绕过 Excel 要求确认删除工作表？
回答by JMax
You can change the default display alert parameter of Excel using:
您可以使用以下方法更改 Excel 的默认显示警报参数：
Application.DisplayAlerts = False
don't forget to restore the standard behavior at the end of your process:
不要忘记在流程结束时恢复标准行为：
Application.DisplayAlerts = True
回答by Felfrag
I ran into this issue using Excel 2016, and surprisingly DisplayAlerts was useless. Not sure if anyone else has experienced this. I'm still unsure as to why, but reading this thread, according to the remarks of the Worksheet.Delete method (here):
我在使用 Excel 2016 时遇到了这个问题，令人惊讶的是 DisplayAlerts 没用。不确定其他人是否经历过这种情况。我仍然不确定为什么，但根据 Worksheet.Delete 方法（此处）的注释阅读此线程：
When you delete a Worksheet , this method displays a dialog box that prompts the user to confirm the deletion. This dialog box is displayed by default. When called on the Worksheet object, the Delete method returns a Boolean value that is False if the user clicked Cancel on the dialog box or True if the user clicked Delete.
删除工作表时，此方法会显示一个对话框，提示用户确认删除。默认情况下会显示此对话框。当在 Worksheet 对象上调用时，Delete 方法返回一个布尔值，如果用户单击对话框上的取消，则该值为 False，如果用户单击删除，则返回 True。
In Excel 2016, though Application.DisplayAlerts was set to False, it kept showing the alert after (or rather before) deletion.
在 Excel 2016 中，虽然 Application.DisplayAlerts 被设置为 False，但它在删除之后（或者更确切地说是之前）一直显示警报。
I haven't found a true work around yet, so I'm simply making the sheets I want to delete "disappear" using a for eachloop:
我还没有找到真正的解决方法，所以我只是使用for each循环使我想删除的工作表“消失” ：
Sht.UsedRange.clear
For each shp in sht.Shapes
shp.Delete
Next
For each nm in sht.Parent.Names
if nm.RefersToRange.Parent is sht then nm.Delete
Next
sht.visible = xlSheetVeryHidden
Sht.UsedRange.clear
For each shp in sht.Shapes
shp.Delete
Next
For each nm in sht.Parent.Names
if nm.RefersToRange.Parent is sht then nm.Delete
Next
sht.visible = xlSheetVeryHidden
(code is an unchecked draft; eventual errors can be treated with an on error resume nextmostly)
（代码是未经检查的草稿；最终的错误可以用on error resume next主要的方式处理）
It's far from ideal, but it does what I need done (at the cost of more memory, sure). Maybe I should turn this reply into a question and see if someone has a better idea for Excel 2016.
它远非理想，但它完成了我需要做的事情（当然，以更多的内存为代价）。也许我应该把这个回复变成一个问题，看看是否有人对 Excel 2016 有更好的想法。
回答by Manjula
TO DELETE ALL SHEETS WITH OUT "REPORT" SHEET **
删除没有“报告”表的所有表**
Dim NM As String
Dim CTS As Integer
Dim CNT2 As Integer
Dim CNT3 As Integer
CNT3 = 1
CNT2 = 1
CTS = Sheets.Count
Do Until CNT2 = CTS + 1
NM = Sheets(CNT3).Name
If Name = "Report" Then
Range("A1").Select
CNT3 = CNT3 + 1
Else
Sheets(NM).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If
CNT2 = CNT2 + 1
Loop
