Excel 2019

Application WorkbookBeforePrint & WorkbookModelChange Events

In this tutorial, you’ll learn how to use WorkbookBeforePrint, and WorkbookModelChange events. These are the application-level events, WorkbookBeforePrint triggered before printing any workbook and WorkbookModelChange triggered when the data model is changed. 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

WorkbookBeforePrint

Syntax: App_WorkbookBeforePrint(Wb , Cancel)

The WorkbookBeforePrint event occurs before printing anything from any workbook. The App_WorkbookBeforePrint procedure has two arguments:

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

App_WorkbookBeforePrint Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
 If Wb.Name = "brainbell.xlsm" Then
  Cancel = True
  MsgBox "Printing disalbed in Application WorkbookBeforePrint"
 End If
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

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

WorkbookModelChange

Syntax: App_WorkbookModelChange(Wb, Changes)

The WorkbookModelChange event occurs after the data model is changed. The App_WorkbookModelChange procedure has two arguments:

  1. Wb
    The Workbook
  2. Changes
    The type of change that you made to the Data Model

App_WorkbookModelChange Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookModelChange(ByVal Wb As Workbook, ByVal Changes As ModelChanges)
 MsgBox "Application WorkbookModelChange : " & Wb.Name
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

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

Advertisement:
Advertisement: