Handling workbook close events

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

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!