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

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?

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


"andriil" <andriil@discussions.microsoft.com> wrote in message
news:6F897E2B-3673-4702-880A-2E1FA11810B7@microsoft.com...
> 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?

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

On Fri, 16 Dec 2005 05:37:03 -0800, "andriil"
<andriil@discussions.microsoft.com> wrote:

>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?

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???).

andriil wrote:
>
> 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?

--

Dave Peterson

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.

"Bernie Deitrick" wrote:

> 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
>
>
> "andriil" <andriil@discussions.microsoft.com> wrote in message
> news:6F897E2B-3673-4702-880A-2E1FA11810B7@microsoft.com...
> > 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?
>
>
>

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.

"Gord Dibben" wrote:

> 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
>
> On Fri, 16 Dec 2005 05:37:03 -0800, "andriil"
> <andriil@discussions.microsoft.com> wrote:
>
> >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?
>

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" wrote:

> 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???).
>
> andriil wrote:
> >
> > 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?
>
> --
>
> 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.

andriil wrote:
>
> 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" wrote:
>
> > 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???).
> >
> > andriil wrote:
> > >
> > > 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?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

"Dave Peterson" wrote:

> 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.
>
> andriil wrote:
> >
> > 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" wrote:
> >
> > > 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???).
> > >
> > > andriil wrote:
> > > >
> > > > 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?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

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

On Fri, 16 Dec 2005 10:22:33 -0600, Dave Peterson <petersod@verizonXSPAM.net>
wrote:

>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???).
>
>andriil wrote:
>>
>> 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?

Try opening xl in safe mode.

Do you get the prompt then?

Gord Dibben wrote:
>
> 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
>
> On Fri, 16 Dec 2005 10:22:33 -0600, Dave Peterson <petersod@verizonXSPAM.net>
> wrote:
>
> >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???).
> >
> >andriil wrote:
> >>
> >> 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?

--

Dave Peterson

yes

"Dave Peterson" wrote:

> Try opening xl in safe mode.
>
> Do you get the prompt then?
>
> Gord Dibben wrote:
> >
> > 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
> >
> > On Fri, 16 Dec 2005 10:22:33 -0600, Dave Peterson <petersod@verizonXSPAM.net>
> > wrote:
> >
> > >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???).
> > >
> > >andriil wrote:
> > >>
> > >> 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?
>
> --
>
> Dave Peterson
>

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

On Fri, 16 Dec 2005 09:19:02 -0800, "andriil"
<andriil@discussions.microsoft.com> wrote:

>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.
>
>"Gord Dibben" wrote:
>
>> 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
>>
>> On Fri, 16 Dec 2005 05:37:03 -0800, "andriil"
>> <andriil@discussions.microsoft.com> wrote:
>>
>> >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?
>>

I don't.

andriil wrote:
>
> yes
>
> "Dave Peterson" wrote:
>
> > Try opening xl in safe mode.
> >
> > Do you get the prompt then?
> >
> > Gord Dibben wrote:
> > >
> > > 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
> > >
> > > On Fri, 16 Dec 2005 10:22:33 -0600, Dave Peterson <petersod@verizonXSPAM.net>
> > > wrote:
> > >
> > > >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???).
> > > >
> > > >andriil wrote:
> > > >>
> > > >> 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?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

And you're using xl2003?

andriil wrote:
>
> yes
>
> "Dave Peterson" wrote:
>
> > Try opening xl in safe mode.
> >
> > Do you get the prompt then?
> >
> > Gord Dibben wrote:
> > >
> > > 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
> > >
> > > On Fri, 16 Dec 2005 10:22:33 -0600, Dave Peterson <petersod@verizonXSPAM.net>
> > > wrote:
> > >
> > > >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???).
> > > >
> > > >andriil wrote:
> > > >>
> > > >> 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?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

On Fri, 16 Dec 2005 12:54:46 -0600, Dave Peterson <petersod@verizonXSPAM.net>
wrote:

>Try opening xl in safe mode.
>
>Do you get the prompt then?
>
>Gord Dibben wrote:
>>
>> 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
>>
>> On Fri, 16 Dec 2005 10:22:33 -0600, Dave Peterson <petersod@verizonXSPAM.net>
>> wrote:
>>
>> >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???).
>> >
>> >andriil wrote:
>> >>
>> >> 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?

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



Gord Dibben wrote:
>
> 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
>
> On Fri, 16 Dec 2005 12:54:46 -0600, Dave Peterson <petersod@verizonXSPAM.net>
> wrote:
>
> >Try opening xl in safe mode.
> >
> >Do you get the prompt then?
> >
> >Gord Dibben wrote:
> >>
> >> 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
> >>
> >> On Fri, 16 Dec 2005 10:22:33 -0600, Dave Peterson <petersod@verizonXSPAM.net>
> >> wrote:
> >>
> >> >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???).
> >> >
> >> >andriil wrote:
> >> >>
> >> >> 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?

--

Dave Peterson