For a modified version of the above code (prevents it from triggering when renamed)
This workbook code:
Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
shName = GetSheetNameFromCodeName(sh.CodeName)
Application.OnTime Now + TimeSerial(0, 0, 1), "DeleteSheet"
End Sub
Module code:
Public shName As String
Sub Deletesheet()
Dim oWS As Object
On Error Resume Next
Set oWS = Sheets(shName)
If oWS Is Nothing Then MsgBox shName & " has been deleted"
End Sub
Public Function GetSheetNameFromCodeName(ByVal pCodeName As String, Optional wb) As String
'courtesy of xld, next post
If IsMissing(wb) Then Set wb = ThisWorkbook
GetSheetNameFromCodeName = wb.VBProject.VBComponents(pCodeName).Properties("Name")
End Function
Matt