Suppress worksheet delete verification msgbox
 

I have a workbook that creates new worksheets using a template worksheet and
a data worksheet (kind of like a Word mailmerge). An index worksheet is also
updated with the names of the created worksheets and hyperlinks to them.
When I want to clear the workbook, I loop through the index worksheet
calling the following macro that deletes the created worksheets. I also
delete entries in the index.

Sub Remove_Worksheet(WorksheetName As String)
Sheets(WorksheetName).Select
ActiveWindow.SelectedSheets.Delete
End Sub

This macro works fine, but a verification message is displayed each time a
worksheet is to be deleted.

Data may exist in the sheet(s) selected for deletion. To permanently delete
the data, press Delete.

Delete Cancel

I was wondering if there is a command that will prevent this message box
from being displayed and allow the worksheet to be deleted without requiring
an individual user provided response for each one? Thanks.

Suppress worksheet delete verification msgbox
 

Application.DisplayAlerts = False
;
your code
;
Application.DisplayAlerts = True

Regards

Trevor


"CurtB" wrote in message
...
I have a workbook that creates new worksheets using a template worksheet
and
a data worksheet (kind of like a Word mailmerge). An index worksheet is
also
updated with the names of the created worksheets and hyperlinks to them.
When I want to clear the workbook, I loop through the index worksheet
calling the following macro that deletes the created worksheets. I also
delete entries in the index.

Sub Remove_Worksheet(WorksheetName As String)
Sheets(WorksheetName).Select
ActiveWindow.SelectedSheets.Delete
End Sub

This macro works fine, but a verification message is displayed each time a
worksheet is to be deleted.

Data may exist in the sheet(s) selected for deletion. To permanently
delete
the data, press Delete.

Delete Cancel

I was wondering if there is a command that will prevent this message box
from being displayed and allow the worksheet to be deleted without
requiring
an individual user provided response for each one? Thanks.

Suppress worksheet delete verification msgbox
 

Thanks, Trevor.

"Trevor Shuttleworth" wrote:

Application.DisplayAlerts = False
;
your code
;
Application.DisplayAlerts = True

Regards

Trevor


"CurtB" wrote in message
...
I have a workbook that creates new worksheets using a template worksheet
and
a data worksheet (kind of like a Word mailmerge). An index worksheet is
also
updated with the names of the created worksheets and hyperlinks to them.
When I want to clear the workbook, I loop through the index worksheet
calling the following macro that deletes the created worksheets. I also
delete entries in the index.

Sub Remove_Worksheet(WorksheetName As String)
Sheets(WorksheetName).Select
ActiveWindow.SelectedSheets.Delete
End Sub

This macro works fine, but a verification message is displayed each time a
worksheet is to be deleted.

Data may exist in the sheet(s) selected for deletion. To permanently
delete
the data, press Delete.

Delete Cancel

I was wondering if there is a command that will prevent this message box
from being displayed and allow the worksheet to be deleted without
requiring
an individual user provided response for each one? Thanks.

Suppress worksheet delete verification msgbox
 

This works fine, but what if I do NOT want a user to be able to delete a
worksheet (by right-clicking the sheet tab, then click delete). How can I
capture the event that a user right clicks a worksheet tab ? The
'beforerightclick' event works all over the sheet, but except for the tab. Do
I have to set 'Target' to a secret 'range' ?

"CurtB" wrote:

I have a workbook that creates new worksheets using a template worksheet and
a data worksheet (kind of like a Word mailmerge). An index worksheet is also
updated with the names of the created worksheets and hyperlinks to them.
When I want to clear the workbook, I loop through the index worksheet
calling the following macro that deletes the created worksheets. I also
delete entries in the index.

Sub Remove_Worksheet(WorksheetName As String)
Sheets(WorksheetName).Select
ActiveWindow.SelectedSheets.Delete
End Sub

This macro works fine, but a verification message is displayed each time a
worksheet is to be deleted.

Data may exist in the sheet(s) selected for deletion. To permanently delete
the data, press Delete.

Delete Cancel

I was wondering if there is a command that will prevent this message box
from being displayed and allow the worksheet to be deleted without requiring
an individual user provided response for each one? Thanks.

Suppress worksheet delete verification msgbox
 

When that workbook opens (in the workobook_open Event):
Private Sub Workbook_Open()
Application.CommandBars("Ply").Controls("Delete"). Enabled = False
End Sub

and before that workbook closes:

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

maybe also include this:
Private Sub Workbook_Activate()
Application.CommandBars("Ply").Controls("Delete"). Enabled = False
End Sub

HTH

"nb0512" wrote in message
...
This works fine, but what if I do NOT want a user to be able to delete a
worksheet (by right-clicking the sheet tab, then click delete). How can I
capture the event that a user right clicks a worksheet tab ? The
'beforerightclick' event works all over the sheet, but except for the tab.
Do
I have to set 'Target' to a secret 'range' ?

"CurtB" wrote:

I have a workbook that creates new worksheets using a template worksheet
and
a data worksheet (kind of like a Word mailmerge). An index worksheet is
also
updated with the names of the created worksheets and hyperlinks to them.
When I want to clear the workbook, I loop through the index worksheet
calling the following macro that deletes the created worksheets. I also
delete entries in the index.

Sub Remove_Worksheet(WorksheetName As String)
Sheets(WorksheetName).Select
ActiveWindow.SelectedSheets.Delete
End Sub

This macro works fine, but a verification message is displayed each time
a
worksheet is to be deleted.

Data may exist in the sheet(s) selected for deletion. To permanently
delete
the data, press Delete.

Delete Cancel

I was wondering if there is a command that will prevent this message box
from being displayed and allow the worksheet to be deleted without
requiring
an individual user provided response for each one? Thanks.

Suppress worksheet delete verification msgbox
 

Thanks Bob ! Works fine.

"Bob Umlas" wrote:

When that workbook opens (in the workobook_open Event):
Private Sub Workbook_Open()
Application.CommandBars("Ply").Controls("Delete"). Enabled = False
End Sub

and before that workbook closes:

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

maybe also include this:
Private Sub Workbook_Activate()
Application.CommandBars("Ply").Controls("Delete"). Enabled = False
End Sub

HTH

"nb0512" wrote in message
...
This works fine, but what if I do NOT want a user to be able to delete a
worksheet (by right-clicking the sheet tab, then click delete). How can I
capture the event that a user right clicks a worksheet tab ? The
'beforerightclick' event works all over the sheet, but except for the tab.
Do
I have to set 'Target' to a secret 'range' ?

"CurtB" wrote:

I have a workbook that creates new worksheets using a template worksheet
and
a data worksheet (kind of like a Word mailmerge). An index worksheet is
also
updated with the names of the created worksheets and hyperlinks to them.
When I want to clear the workbook, I loop through the index worksheet
calling the following macro that deletes the created worksheets. I also
delete entries in the index.

Sub Remove_Worksheet(WorksheetName As String)
Sheets(WorksheetName).Select
ActiveWindow.SelectedSheets.Delete
End Sub

This macro works fine, but a verification message is displayed each time
a
worksheet is to be deleted.

Data may exist in the sheet(s) selected for deletion. To permanently
delete
the data, press Delete.

Delete Cancel

I was wondering if there is a command that will prevent this message box
from being displayed and allow the worksheet to be deleted without
requiring
an individual user provided response for each one? Thanks.