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.
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:
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 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 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 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: