The Workbook_BeforeClose event in not working

[sbuffington]

I created the popup calendar from Martin Green's site, saved it in a personal project like it said, and I keep getting the "Insert Date" adding to the context menuwhen you right click in a cell tofirethe popup calendar. The only thing that I can imagine is that the Workbook_BeforeClose code is not working. I've tried including the Application.EnableEvent=true in immediate window and that doesnt seem to work either.

Private Sub Workbook_Open()
    Dim NewControl As CommandBarControl
    Application.OnKey "+^{C}", "Module1.OpenCalendar"
    Set NewControl = Application.CommandBars("Cell").Controls.Add
    With NewControl
        .Caption = "Insert Date"
        .OnAction = "Module1.OpenCalendar"
        .BeginGroup = True
    End With
    Application.EnableEvents = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub

 

[rlv01]

Yes; that event will fire when Personal.xlsb is closed.

Not in my experience. I recall that there is an exception for that event for the personal macro workbook (but I cannot find the original reference). I just re-tested under both XL2010 and 2016. The Before_Close event in personal.xlsb did not fire when excel is closed.

 

[rlv01]

The event will not fire from the personal workbook

Try using auto_close (note that this must go in a standard code module, and not in the workbook code module). That should work with personal.xlsb

Sub Auto_Close()
    Dim Msg As String

    Msg = "This is the Auto_Close subroutine for " & ThisWorkbook.Name & vbCr & vbCr
    Msg = Msg & "Workbook event status: " & Application.EnableEvents
    MsgBox Msg, vbInformation, ThisWorkbook.Name

    On Error Resume Next
    Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub

 

[shg]

If you want to keep the event in Personal, put this in Personal's ThisWorkbook module:

Option Explicit

Dim WithEvents app  As Excel.Application

Private Sub Workbook_Open()
  Set app = Excel.Application
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
  ' do stuff here
End Sub

You'll need to close and restart Excel (or run Workbook_Open) to initialize.

 

[sbuffington]

Thank you so much rlv01...this solution works, I really appreciate it.

 

[sbuffington]

I tried this solution as well, but it did not work, thank you for the help I really appreciate it