Excel

Application WorkbookAfterSave, WorkbookBeforeSave, WorkbookBeforeClose & WorkbookOpen Events

In this tutorial, you’ll learn how to capture WorkbookAfterSave, WorkbookBeforeSave, WorkbookBeforeClose and WorkbookOpen events. These are the application-level events and triggered when any workbook is opened, saved, or closed. Let’s open VBE (press Alt+F11) and write the following code in ThisWorkbook code module. This code enables your workbook to listen to application object events (for details, visit how to capture application events):

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

WorkbookAfterSave

Syntax: App_WorkbookAfterSave(Wb, Success)

The WorkbookAfterSave event occurs after any workbook is saved. The App_WorkbookAfterSave procedure has two arguments:

  1. Wb is the saved workbook
  2. Success returns True if the save operation was successful; otherwise, False.

App_WorkbookAfterSave Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookAfterSave(ByVal Wb As Workbook, ByVal Success As Boolean)
 If Success = True Then
  MsgBox Wb.Name & " Saved"
 Else
  MsgBox Wb.Name & " Not Saved"
 End If
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookAfterSave is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_AfterSave procedure.

WorkbookBeforeSave

Syntax: App_WorkbookBeforeSave(Wb, SaveAsUI, Cancel)

The WorkbookBeforeSave event occurs before any workbook is saved. The App_WorkbookBeforeSave procedure accepts three arguments:

  1. Wb
    The workbook
  2. SaveAsUI return True if:
    • the file is not already saved and a Save this file dialog box is displayed
    • the file is read-only and Save As dialog box is displayed
    • you clicked Save As button and Save As dialog box is displayed
  3. Cancel
    Setting Cancel to True prevents the workbook from being saved.

App_WorkbookBeforeSave Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
 If SaveAsUI = True Then
  Cancel = True
  MsgBox "Save As not allowed"
 End If
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookBeforeSave is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_BeforeSave procedure.

WorkbookBeforeClose

Syntax: App_WorkbookBeforeClose(Wb, Cancel)

The WorkbookBeforeClose event occurs before any workbook is closed. The App_WorkbookBeforeClose procedure has two arguments:

  1. Wb
    The workbook
  2. Cancel
    Setting Cancel to True prevents the workbook from closing

App_WorkbookBeforeClose Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
 If Wb.Name = "brainbell.xlsm" Then
  Cancel = True
  MsgBox "Application WorkbookBeforeClose"
 End If
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookBeforeClose is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_BeforeClose procedure.

WorkbookOpen

Syntax: App_WorkbookOpen(Wb)

The WorkbookOpen event occurs when any workbook is opened. The App_WorkbookOpen procedure uses one argument Wb which represents the opened workbook.

App_WorkbookOpen Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
 MsgBox Wb.Name & " opened"
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookOpen is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_Open procedure.