Do you want to take some action when the user closes a workbook ?
Fortunately Excel provide an event handler for this WorkbookBeforeClose.
But, still there’s a catch, this event is raise before Excel asks the user
whether he wants to save/discard eventual change (or even cancel the closing).
Add a Class Module to your .xla and use the following code
Public WithEvents TheApp As Excel.Application Private Sub Class_Initialize() Set TheApp = Application End Sub Private Sub TheApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean) If (Not Wb.Saved) And (Not Wb.IsAddin) Then msg = "Do you want to save the changes you made to " msg = msg & Wb.Name & "?" Ans = MsgBox(msg, vbExclamation + vbYesNoCancel) Select Case Ans Case vbYes ' Do something Wb.Save Case vbNo ' Do something Wb.Saved = True Case vbCancel Cancel = True Exit Sub End Select End If ' Do something End Sub
I left out “Option Explicit” and variable declarations.
The Not Wb.IsAddin
is used because Excel does not usually asks the user whether he wants to save addins workbooks.
Advertisements