Delete Sheet Event

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
Steve

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,884
Putting this in the ThisWorkbook code module should do what you want.
Code:
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
 

Forum statistics

Threads
1,085,495
Messages
5,384,004
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top