Excel 2019

Workbook Open and Before Close Events

In this tutorial, you’ll learn how to use the Workbook_Open and Workbook_BeforeClose event procedures. Open a workbook and press Alt + F11 to open Visual Basic Editor (VBE). Then double-click the ThisWorkbook object in the Project Explorer tree to show its code module.

Workbook Open and BeforClose Events

Workbook_Open

Syntax: Workbook_Open()

One of the most common monitored events is the Open event for a workbook. The Open event is triggered when the workbook is opened and executes the procedure named Workbook_Open. The Workbook_Open procedure is perfect for such tasks as these:

  • Informing users about the workbook features
  • Displaying a welcome message
  • Activating a particular worksheet or cell
  • Opening other workbooks
  • … and many more

Here’s an example of the Workbook_Open procedure which check the contents of active worksheet when you open the workbook.

Workbook_Open Example:

Private Sub Workbook_Open()
 If ActiveSheet.ProtectContents Then
  MsgBox ActiveSheet.Name & " is protected"
 Else
  MsgBox ActiveSheet.Name & " is not protected"
 End If
End Sub

Workbook_BeforeClose

Syntax: Workbook_BeforeClose (Cancel)

The Workbook_BeforeClose event handler procedure is executed automatically immediately before the workbook is closed. Here’s an example of the Workbook_BeforeClose procedure which saves the workbook automatically before closing it, so the Excel will not show you the prompt that ask you if you want to save your changes.

Workbook_BeforeClose Examples:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 ThisWorkbook.Save
End Sub

The Cancel argument can be set to True or False. Setting Cancel argument to True cancels the close operation and the workbook is left open:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Cancel = True
End Sub
Advertisement:
Advertisement: