Delete Sheets and avoid Excel asking the user to confirm, using custom messages instead

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.

Then comes the sub event of deleting the sheet, and Excel pops up its own window for 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.

How can I bypass Excel asking to confirm the deletion of a sheet ?

Solution 1:

You can change the default display alert parameter of Excel using:

Application.DisplayAlerts = False

don't forget to restore the standard behavior at the end of your process:

Application.DisplayAlerts = True

Solution 2:

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

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.

In Excel 2016, though Application.DisplayAlerts was set to False, it kept showing the alert after (or rather before) deletion.

I haven't found a true work around yet, so I'm simply making the sheets I want to delete "disappear" using a for each loop:

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 next mostly)

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.

Solution 3:

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