Delete Sheet Event

[ElvisSteel]

Is there a way of detecting the deletion of a worksheet. I have a workbook that includes a drop down list of the current worksheets. So if a worksheet is deleted, I need to update this list.
Is there a way of trapping this event?

Thanks

Steve

 

[Norie]

Steve

I think the short answer is no, there is no worksheet delete event.

 

[mikerickson]

Putting this in the ThisWorkbook code module should do what you want.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Static sheetCount As Long
    If sheetCount <> 0 Then
        Select Case sheetCount - Sheets.Count
            Case Is > 0
                MsgBox "Sheet deleted"
            Case Is < 0
                MsgBox "Sheet added"
        End Select
    End If
    sheetCount = Sheets.Count
End Sub

 

[ElvisSteel]

Thanks very much, that works perfectly!