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


    Richard

  • Re: Capture Sheet Delete Event


    Code
    Sub DeleteSheet()
    Application.DisplayAlerts = False
        Sheets("Sheet3").Delete
    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
    [vba]
    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
    ErrDelete:
    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
    [/vba]


    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!