Skip navigation

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: