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


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"
  MsgBox ActiveSheet.Name & " is not protected"
 End If
End Sub


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)
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