Is there a Sheet delete property?

  • Thread starter Thread starter David
  • Start date Start date
D

David

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/28/2003
ActiveSheet.Select
ActiveWindow.SelectedSheets.Delete
End Sub
 
Does anyone have any ideas on my original question? See my first post
in this message??
 
Hi Rick:

There is no trappable Worksheet_Delete event. A workaround might be to use
the Deactivate event and then check if the worksheet still exists.

Regards,

Vasant.
 
Thanks Vasant,

Do you know if there is a way to cancel a sheet being deleted once a
user has selected delete on the excel delete message box? Basically i am
trying to stop users from deleteing a sheet if it has certain data in
it. I don't want to protect the worksheet/workbook.. I will be doing my
checking in the deactivate event and if certain criteria are meet then i
need to cancel the delete..

Any ideas?

Rick
 
Sorry, Rick; AFAIK there is no way to prevent deletion of a sheet (without
protecting the workbook).

Regards,

Vasant.
 
Does anyone have any ideas on my original question? See my first post
in this message??
Click to expand...

Yes, make Excel's two Delete Sheet buttons run your own macro instead.

Private Sub Workbook_Open()
CommandBars("Edit").FindControl(ID:=847).OnAction = "MySheetDeleteMacro"
CommandBars("Ply").FindControl(ID:=847).OnAction = "MySheetDeleteMacro"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars("Edit").FindControl(ID:=847).OnAction = ""
CommandBars("Ply").FindControl(ID:=847).OnAction = ""
End Sub

Sub MySheetDeleteMacro()
MsgBox "Do you really want to delete " & ActiveSheet.Name & " ?"
End Sub


Regards,
Vic Eldridge
 
Back
Top