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 the future efficiently.

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

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

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 from 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. The relevant procedure for this event is Workbook_Open.

You can design the Workbook_Open procedure to perform the following tasks:

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

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

  • If the user makes the macros disable.
  • If the user holds the 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 shown.

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:

Worbook-level Events and Their Uses

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
    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 activates 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
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 the 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 the 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 a 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 ☕

Download Working File

Download the working file from the link below:


Read More…



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply