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]

Testing the Workbook_BeforeClose event is easy enough. Adding a msgbox statement as the second line should tell you if the event is firing.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "This is the Workbook_BeforeClose event"

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

Also a subroutine like this that you run manually from the debug window:

Sub EventsOn()
    Application.EnableEvents = True
    MsgBox "Events Status: " & Application.EnableEvents
End Sub

can be useful for those times when you are unsure if events are enabled.

 

[sbuffington]

I added to code suggested and still no before close event

 

[rlv01]

Create a new workbook and add this code to the workbook code module

Private Sub Workbook_Open()
 MsgBox "This is the Workbook_Open event", vbInformation, ThisWorkbook.Name
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "This is the Workbook_BeforeSave event", vbInformation, ThisWorkbook.Name
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "This is the Workbook_BeforeClose event", vbInformation, ThisWorkbook.Name
End Sub

Then save, close, and reopen the new workbook to test event functionality. If the events fire in your new workbook then they should fire in your current workbook, and you should inspect your code more closely to determine where you might be turning off events.

 

[sbuffington]

Thank you for the response...the code you supplied works as expected. Could the issue be that my code is in a personal.xlsb workbook. Basiclly the only thing the sersonal does is allow me to use a pop up calendar by right in a cell, the workbook close event is to remove the insert date context menu item

 

[shg]

Could the issue be that my code is in a personal.xlsb workbook.

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

 

[sbuffington]

The event will not fire from the personal workbook

 

[shg]

It only fires when the workbook it's in closes; that's Personal.xlsb.

 

[sbuffington]

So does personal stay open after closing excel.

 

[shg]

No.