Excel VBA Workbook Level Events and Their Uses

In this article, I am going to introduce you to the Excel Workbook-level events. This introduction will help you to use them in future efficiently.

Workbook-level events work for a particular workbook. The following table holds all the events name and brief descriptions related to a Workbook.

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

Table of Contents

Table: Workbook Related Events
Using Workbook Event: Open
Using Workbook Event: SheetActivate
Using Workbook Event: NewSheet
Using Workbook Event: BeforeSave
Using Workbook Event: BeforeClose
Download Working File
Read More…

Table: Workbook Related Events

Event Name	Action that Executes the Event
Activate	When the workbook is activated, this event executes.
AfterSave	After the workbook is saved, this event executes.
BeforeClose	When the workbook is closed, just before closing this event executes.
BeforePrint	Just before printing any content of the workbook, this event executes.
BeforeSave	Just before the workbook is saved, this event executes.
Deactivate	When the workbook is deactivated, this event executes.
NewSheet	When a new sheet is created in the workbook, this event executes.
Open	When the workbook is opened.
SheetActivate	When any sheet in the workbook is activated.
SheetBeforeDoubleClick	Just before a worksheet in the workbook is double-clicked. This event executes before the default double-click action.
SheetBeforeRightClick	Just before any worksheet is right-clicked. This event executes before the default right-click action.
SheetChange	When any worksheet is changed by the user.
SheetDeactivate	When any worksheet in the workbook is deactivated.
SheetSelectionChange	The selection on any worksheet is changed.
WindowActivate	When any window of the workbook is activated.
WindowDeactivate	Any workbook window is deactivated.

Caution: In the following paragraphs, I shall present some important Workbook-related events that you will use time to time.

Remember, workbook related event-handler procedures(VBA codes) must be located in the code module for the ThisWorkbook object. If you wrongly put them into any other code module, they won’t work.

Workbook_Open, Workbook_SheetActivate, Workbook_NewSheet, and Workbook_BeforeSave procedures have been included in the ThisWorkbook code module.

Travel from one worksheet to another, save the workbook, create a new sheet and close the workbook. You will find the changes working under the scene.

Using Workbook Event: Open

Open is one of the most important workbook events. This event is executed when the workbook opens. Relevant procedure for this event is Workbook_Open.

You can design Workbook_Open procedure to perform the following tasks:

You can display welcome message to the user.
When user is opening other workbooks.
When user is activating a specific sheet.

Caution
It is not guaranteed that your Workbook_Open procedure will be executed all the time. For two cases, I can make sure that Workbook-Open procedure will not work:

If the user make the macros disable.
If the user holds Shift key while opening a workbook.

Okay, I want to create a simple Workbook_Open procedure that will show a message box reminding the user to keep a file backup when it is Friday. If it is not Friday, nothing will be showed.

Private Sub Workbook_Open()
    If Weekday(Now) = 6 Then
      Msg = "Today is Friday! Make sure you do your weekly Backup please!"
      MsgBox Msg, vbInformation
    End If
End Sub

A message box with some information or direction will pop-up when you open your Excel file on Friday. Image below:

A message with some information popped up when I opened the Excel file on Friday.

You can do a series of actions when the workbook is opened. For example, I have made another VBA code to do the following jobs:

It will maximize the workbook window.
It will activate the worksheet named DataEntry, if there is not sheet named DataEntry, there will an error.
It will select the first empty cell in column A and enter the current date in this cell.

Private Sub Workbook_Open()
    ActiveWindow.WindowState = xlMaximized
    Worksheets(“DataEntry”).Activate
    Range(“A1”).End(xlDown).offset(1,0).Select
    ActiveCell.Value = Date
End Sub

Using Workbook Event: SheetActivate

Workbook’s “SheetActivate” event is executed when a user activates any worksheet in the workbook.

Whatever worksheet a user activate in the workbook, the following procedure will select cell B3. “On Error Resume Next” statement makes the procedure to ignore the error that occurs if the activated sheet is a chart sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  On Error Resume Next
  Range("B3").Select
End Sub

There is an alternative method to check whether the activated sheet is a worksheet or not. See the following code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If TypeName(Sh) = "Worksheet" Then Range("B3").Select
End Sub

Using Workbook Event: NewSheet

The following procedure executes whenever a new sheet is added to the workbook.

The following NewSheet event procedure will check at first whether the new sheet is a worksheet or a chart sheet, if new sheet is a worksheet, this procedure will insert the date and time stamp into cell A1.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   If TypeName(Sh) = "Worksheet" Then
   Range("A1") = "This Sheet added @ " & Now()
   End If
End Sub

Using Workbook Event: BeforeSave

The BeforeSave workbook event occurs before the workbook is actually saved. We save Excel file choosing File ➪ Save command; this command displays the Save As dialog box when the file is going to be saved for the first time or was opened in read-only mode.

The Workbook_BeforeSave procedure has two arguments: SaveAsUI and Cancel. Both are Boolean type arguments. See the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   If SaveAsUI Then
   MsgBox "Use the new file-naming convention to save your file."
   End If
End Sub

When the save operation brings up the Save As dialog box, the SaveAsUI variable is TRUE and a message box with information “Use the new file-naming convention to save your file.” is displayed.

The BeforeSave event procedure also has another argument named Cancel. By default its value is FALSE. If the procedure sets this argument in such way that it may be TRUE, then the file is not saved.

Using Workbook Event: BeforeClose

The BeforeClose event occurs before a workbook is closed. You may use this event in conjunction with a Workbook_Open event handler.

For example, you may want to initialize some items in your workbook when the workbook is opened using the Workbook_Open procedure and clean up that initialization when you close the workbook using the Workbook_BeforeClose procedure.

If you try to close a workbook that hasn’t been saved yet, Excel displays a prompt that asks whether you want to save the workbook before it closes.

So, this was all from me on this topic. Let me know in case you face any issues while using this article.

Happy Excelling ☕