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
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
Have you considered using before BeforeClose? This might be a better alternative.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With