Suppress worksheet delete verification msgbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Application.DisplayAlerts = False
;
your code
;
Application.DisplayAlerts = True

Regards

Trevor
 
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' ?
 
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
 
Thanks Bob ! Works fine.

Bob Umlas said:
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
Click to expand...
 
Back
Top