How can I disable system alerts (deleting a sheet for example)?

[Guest]

Every time delete a sheet, I get a system alert saying "Data may exist in the
sheet(s) selected for deletion. To permanently delete the data, press
Delete". It's a bit annoying when you have to delete many sheets and you
don't want to select multiple sheets. Is there any possibility to disable
this alert?

 

[Bernie Deitrick]

Use a macro. Copy the macro below into your personal.xls file, and then assign it to a custom
commandbar button.

HTH,
Bernie
MS Excel MVP

Sub KillSheet()
Application.DisplayAlerts = False
On Error GoTo NoWorkbooks
If ActiveSheet.Type = 3 Then
ActiveChart.Delete
Else
If ActiveWindow.SelectedSheets.Count < _
ActiveWorkbook.Worksheets.Count Then
ActiveWindow.SelectedSheets.Delete
Else
If MsgBox("That's the last sheet." & Chr(10) & _
"Do you want to close the file without saving?", _
vbYesNo) = vbYes Then
ActiveWorkbook.Close False
End If
End If
End If
NoWorkbooks:
Application.DisplayAlerts = True
End Sub

 

[Gord Dibben]

Sub SheetDelete()
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub

Assign to a button or shortcut key-combo.


Gord Dibben Excel MVP

 

[Dave Peterson]

You could use a macro to delete the sheet.

But I would bet it would take as long to run the macro as it would to answer the
prompt.

Another option is to upgrade your version of excel. xl2003 doesn't have the
prompt anymore. (Not sure when it was removed, though...xl2002???).

 

[Guest]

Mr. Deitrick,
thank you very much for your answer.
Could you, please, also tell me if there is anything like "deleteworksheet"
event in excel, so that I could tie the macro to this event and delete the
sheets normally, but without the alert?
Thank you.
Andrii.

 

[Guest]

Mr. Dibben,
thank you for the macro.
Is there any way to avoid using some custom buttons? I used to tie my macros
to workbook, worksheet or application events, but I can't find anything like
"worksheet_delete" event or something... Does anything like this exist?
Thanks again.
Andrii.

 

[Guest]

Mr. Peterson,
Thank you for your answer.
I'm afraid, I have the 2003 and I still see the alert...
Maybe it's optional, and I can disable it?
Thanks
Andrii.

 

[Dave Peterson]

Not an option.

Maybe you have something that changes the worksheet -- or are using book.xlt or
sheet.xlt that does something to make it look to excel like the worksheet was
used.

 

[Guest]

It was used. But I want to delete it. Without alerts. Thank you, sorry for
bothering.

 

[Gord Dibben]

Dave

The warning message appears in my 2002 and 2003 versions.

Either right-click and delete or delete sheet from toolbar or Insert menu.

Maybe you have customized your "Delete Sheet" button or menu item.


Gord

 

[Dave Peterson]

Try opening xl in safe mode.

Do you get the prompt then?

 

[Guest]

yes

Try opening xl in safe mode.

Do you get the prompt then?

 

[Gord Dibben]

Andrii

You could assign the macro to the Edit>Delete Sheet menu item through
Tools>Customize.

You could also assign it to the Delete Sheet on the sheet tab right-click menu
item.

This would be more complex and would require changing the right-click
menu("Ply")

Private Sub Workbook_Open()
Application.CommandBars("Ply").Controls("Delete").Delete
With Application.CommandBars("Ply").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Delete Sheet no Warning"
.OnAction = "MyMacros.xla" & "!SheetDelete"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Ply").Reset
End Sub


Gord

 

[Dave Peterson]

I don't.

yes

 

[Dave Peterson]

And you're using xl2003?

yes

 

[Gord Dibben]

No, I do not get a warning when opening in safe mode and delete a sheet from
the default Book1.

If I then open an existing workbook, I get the warning when deleting a sheet
since it does have data in it.

Also if I start Excel normally and File...New>Blank Workbook I don't get the
warning when deleting a sheet from that workbook.

Any existing workbook or new from BOOK.XLT gives the warning.

Interesting. Never knew this thing.


Gord

 

[Dave Peterson]

My book.xlt and my sheet.xlt templates have some page layout stuff and
header/footer stuff. Maybe xl2003 is smart enough to notice that it isn't a
"blank canvas".

"tabula rasa" was what my English teachers called it. (First time in 30 years I
could use that!! <vbg>.)