MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is there something similar to NewSheet but

Posted by moujia on May 25, 2000 7:25 PM

activated when you delete a sheet?

Thanks in advance

Posted by Ivan Moala on May 26, 2000 4:58 AM

Do you mean is there an EventHandler for Delete
sheet available ??
Short answer is no....BUT you can simulate one.

Try this

Option Explicit

Dim shName As String
Dim Avail

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
Avail = Sheets(shName).Range("A1")
If Err.Number <> 0 Then
MsgBox shName & " has been Deleted ...Put your routine here to run?"
End If
On Error GoTo 0
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
shName = Sh.Name
End Sub

This works on the fact that the Workbook_SheetDeactivate event is triggered before
the SheetActivate event and stores the Sheet name in variable shName.
The sheetActivate event runs a little routine that
tries to get a value from the lastsheet, which is
shName (from the DeactivateEvent), the on Error Resume next is put there so that if the sheet no longer
exists it generates an error object which is evaluated.
THIS IS WHERE you can put your routine you want
to run when a sheet is deleted.

Now when you delete a sheet the