53

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 ?

BuZz
  • 16,318
  • 31
  • 86
  • 141

3 Answers3

76

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
JMax
  • 26,109
  • 12
  • 69
  • 88
  • 8
    Maybe it's just me, but I like to *restore* the old value instead of setting to `True`. That is, `oldvalue = Application.DisplayAlerts`, `Application.DisplayAlerts = False`, `sheet.Delete`, `Application.DisplayAlerts = oldvalue`. – André Chalella Dec 17 '17 at 18:59
  • @ Andre - Nice touch, def something to keep in mind when changing app settings, you never know what they started out as and may prevent allot of debugging later. – Alfa Bravo Dec 22 '17 at 06:03
4

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.

Felfrag
  • 41
  • 2
  • 1
    If the worksheet has content, the solution is to use Worksheet.ClearContents(), then Worksheet.Save(), and then delete the sheet. Excel is apparently concerned that the sheet has content and only displays the alert if a sheet has content, otherwise it doesn't display the alert. – AWizardInDallas Sep 08 '18 at 08:35
  • @AWizardInDallas: I follow your reasoning, but I do not see what you mean with `Worksheet.Save()`. There are no such thing as a `Save` method for the `Worksheet` object in Excel. There is a `Workbook.Save` method, but it would also save the soon-to-be-deleted sheet on disk or whereever, a bit counter-productive and with a number of probably unwanted side effects. Also @felfrag used `UsedRange.Clear` which does more than ClearContents (it also clears the formats.) – AntoineL Jul 29 '21 at 08:51
2

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
Manjula
  • 21
  • 1