Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA - WorkbookBeforeSave Event Does

Tags:

excel

vba

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
like image 805
PirateGrunt Avatar asked Jan 07 '13 20:01

PirateGrunt


2 Answers

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
like image 195
Francis Dean Avatar answered Oct 04 '22 15:10

Francis Dean


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

like image 26
1dolinski Avatar answered Oct 04 '22 13:10

1dolinski