Excel VBA - WorkbookBeforeSave Event Does

Question

I have an add-in with an application object. The object is declared WithEvents. This enables me to flush some data points to a central database every time the user saves the file. This works most of the time. However, there is one user who quits the Excel application, which calls up the Save dialog box. It appears that quitting Excel with an unsaved file means that the WorkbookBeforeSave event does not fire.

Just to emphasize, I have confirmed that the event does fire when the user hits CTRL-S, or presses the save button. I have also confirmed that the event does NOT fire if I quit the application.

I can think of a few workarounds (automatically save every 10 seconds, for example), but I'm not crazy about that. Can anyone confirm this behavior and/or does anyone have a remedy?

Option Explicit

Private WithEvents mapp As Excel.Application

Private Sub mapp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ' Sanity preservation device
    Msgbox "WorkbookBeforeSave event fired."
    SaveSomeData Wb

End Sub

Answer 1

You could use the Workbook_BeforeClose routine and the Workbook Object's Saved property.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If ThisWorkbook.Saved = False Then
        'Call save function
    End If

End Sub

Answer 2

Have you considered using before BeforeClose? This might be a better alternative.