Capture Sheet Delete Event

  • I wonder how to capture de event when one choose to delete a sheet

    Similar to vbyes or vbno??
    Now it inmediatley goes to my sheet_deactivate_event, but then I don't know if it is beiing deleted?

    I mean capture if you do righclick sheettab


  • Re: Capture Sheet Delete Event

    Sub DeleteSheet()
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
    End Sub

    Quote from Richnl

    I wonder how to capture de event when one choose to delete a sheet

  • Re: Capture Sheet Delete Event

    You can capture the click. Although the user can still press cancel.
    So you will also need to keep a check on the sheet activate deactivate.

    Note this gets more complicated with multi workbooks.

    Class module, Class1
    Private m_objPrevious As Object
    Private m_strPreviousName As String

    Private WithEvents m_cbcPlyDelete As CommandBarButton
    Private WithEvents m_appXL As Application

    Private Sub Class_Initialize()

    Set m_cbcPlyDelete = Application.CommandBars.FindControl(msoControlButton, ID:=847)

    End Sub
    Private Sub m_appXL_SheetActivate(ByVal Sh As Object)
    Dim strName As String

    On Error GoTo ErrDelete
    strName = m_objPrevious.Name

    Exit Sub
    MsgBox "Just Deleted Sheet " & m_strPreviousName
    Exit Sub

    End Sub

    Private Sub m_appXL_SheetDeactivate(ByVal Sh As Object)
    Set m_objPrevious = Sh
    m_strPreviousName = Sh.Name
    End Sub

    Private Sub m_cbcPlyDelete_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)

    MsgBox "About to delete " & ActiveSheet.Name

    End Sub

    Standard code module
    [vba]Public m_clsDeleteSheet As Class1
    Sub StartDeleteWatch()
    Set m_clsDeleteSheet = New Class1
    End Sub[/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!