Workbook Events in VBA
Learn how workbook events work, where to write event procedures, and how to automate tasks when a workbook opens, closes, saves, or changes its window state.
1. What Are Workbook Events?
Workbook events are procedures that execute automatically when a specific event occurs in an Excel workbook. Instead of manually running a macro, you can write an event procedure that responds to an action such as opening or saving a file.
Workbook events are commonly used to display welcome messages, initialize worksheets, record save-related information, validate workbook state, and prepare a workbook for use.
2. Where Do Workbook Events Belong?
Workbook event procedures belong in the ThisWorkbook code module, not in an ordinary standard module.
- Open Excel and press Alt + F11.
- Locate your workbook in Project Explorer.
- Expand the Microsoft Excel Objects folder.
- Double-click ThisWorkbook.
- Choose Workbook from the left object dropdown.
- Select the required event from the right procedure dropdown.
- Write your code inside the generated event procedure.
ThisWorkbook.
3. Workbook_Open Event
The Workbook_Open event runs when the workbook
is opened and the workbook's VBA events are enabled.
Example 1: Display a welcome message
Private Sub Workbook_Open()
MsgBox "Welcome to the Sales Report Workbook!", _
vbInformation, "Welcome"
End Sub
Place this code inside ThisWorkbook.
When the workbook opens, Excel displays the message.
Example 2: Open a specific worksheet
Private Sub Workbook_Open()
ThisWorkbook.Worksheets("Dashboard").Activate
End Sub
Dashboard. Otherwise, the code will raise
an error. For larger projects, validate that the required
worksheet exists before activating it.
4. Workbook_BeforeSave Event
The Workbook_BeforeSave event occurs before
Excel saves the workbook. It provides the
SaveAsUI and Cancel parameters.
You can use it to validate data or cancel a save when
a requirement is not satisfied.
Example 3: Validate a required field before saving
Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Dashboard")
If Len(Trim$(CStr(ws.Range("B2").Value))) = 0 Then
MsgBox "Enter the report title in cell B2 before saving.", _
vbExclamation, "Validation"
Cancel = True
End If
End Sub
How it works
- Excel starts the save operation.
- The event checks cell B2 on the Dashboard worksheet.
- If B2 is blank, a warning appears.
- Setting
Cancel = Truecancels the save operation. - If B2 contains a title, the event allows the save to continue.
5. Workbook_BeforeClose Event
The Workbook_BeforeClose event occurs before
a workbook closes. The Cancel parameter lets
the procedure prevent closing when necessary.
Example 4: Confirm before closing
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim response As VbMsgBoxResult
response = MsgBox( _
"Do you want to close this workbook?", _
vbYesNo + vbQuestion, _
"Confirm Close")
If response = vbNo Then
Cancel = True
End If
End Sub
6. Workbook_BeforePrint Event
The Workbook_BeforePrint event occurs before
a workbook is printed. You can use it to validate a report
or cancel printing if required.
Example 5: Validate before printing
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Dashboard")
If Len(Trim$(CStr(ws.Range("B2").Value))) = 0 Then
MsgBox "Enter a report title before printing.", _
vbExclamation, "Print Validation"
Cancel = True
End If
End Sub
If the report title is missing, the event cancels the
print operation. This example assumes the worksheet named
Dashboard exists.
7. Workbook_Activate and Workbook_Deactivate
These events respond to changes in whether a workbook becomes active or inactive.
Workbook_Activate
Private Sub Workbook_Activate()
Application.StatusBar = "Sales Report Workbook is active."
End Sub
Workbook_Deactivate
Private Sub Workbook_Deactivate()
Application.StatusBar = False
End Sub
8. Workbook_NewSheet Event
The Workbook_NewSheet event occurs when a new
sheet is created in the workbook. The new sheet is passed
into the event procedure as an object parameter.
Example 6: Format a newly created worksheet
Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error GoTo ErrorHandler
If TypeOf Sh Is Worksheet Then
With Sh.Range("A1")
.Value = "New Worksheet"
.Font.Bold = True
.Font.Size = 14
End With
End If
Exit Sub
ErrorHandler:
MsgBox "Unable to format the new sheet: " & Err.Description, _
vbExclamation
End Sub
The procedure checks whether the new sheet is a worksheet before applying cell formatting. This matters because Excel workbooks can also contain chart sheets.
9. Important Workbook Events at a Glance
| Event | When It Occurs | Typical Use |
|---|---|---|
Workbook_Open |
When the workbook opens. | Initialize workbook or display a welcome message. |
Workbook_BeforeSave |
Before the workbook saves. | Validate required information. |
Workbook_BeforeClose |
Before the workbook closes. | Confirm or prevent closing. |
Workbook_BeforePrint |
Before workbook printing. | Validate a report before printing. |
Workbook_Activate |
When the workbook becomes active. | Update workbook-related UI state. |
Workbook_Deactivate |
When the workbook becomes inactive. | Restore temporary application UI changes. |
Workbook_NewSheet |
When a new sheet is created. | Apply default formatting. |
10. Call a Reusable Sub from a Workbook Event
Event procedures should remain easy to maintain. For larger tasks, call a reusable procedure from the event instead of placing every instruction directly inside the event code.
Code in ThisWorkbook
Private Sub Workbook_Open()
InitializeReport
End Sub
Code in a standard module
Public Sub InitializeReport()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Dashboard")
ws.Range("A1").Value = "Sales Dashboard"
ws.Range("A1").Font.Bold = True
ws.Range("A1").Font.Size = 16
End Sub
The workbook event starts the process, while the reusable procedure performs the initialization. This keeps event code short and easier to test.
11. Application.EnableEvents
Excel provides the application property
Application.EnableEvents. When it is False,
Excel events are disabled. This can be useful when a macro
modifies data that would otherwise trigger additional events.
Example 7: Temporarily disable events safely
Option Explicit
Sub UpdateReportSafely()
On Error GoTo ErrorHandler
Application.EnableEvents = False
ThisWorkbook.Worksheets("Dashboard").Range("A1").Value = _
"Report Updated"
CleanUp:
Application.EnableEvents = True
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description, vbExclamation
Resume CleanUp
End Sub
Application.EnableEvents = True after disabling
events. If events remain disabled, workbook and worksheet
event procedures may stop running during later actions.
In a larger application, preserve the previous setting
when appropriate rather than always assuming it was True.
12. How to Test Workbook Events
- Save your workbook as an Excel Macro-Enabled Workbook (
.xlsm). - Open the VBA editor with Alt + F11.
- Double-click
ThisWorkbook. - Write a small event procedure such as
Workbook_Open. - Save the workbook and close it.
- Reopen the workbook in desktop Excel.
- Enable macros only if the workbook and its source are trusted.
- Verify that the expected event procedure runs.
13. Common Mistakes to Avoid
- Writing workbook event code in a standard module instead of
ThisWorkbook. - Using an event procedure name incorrectly.
- Expecting an open event to run when the workbook is already open.
- Forgetting to save the workbook in a macro-compatible format.
- Assuming events will run when macros are blocked or events are disabled.
- Using worksheet names that do not exist.
- Leaving
Application.EnableEventsset to False after an error. - Performing large or slow operations directly inside an event without considering user experience.
- Creating event logic that repeatedly triggers itself without controlling recursion.
14. Practice Exercises
Implement these exercises in a test workbook. Use a copy of important files while learning event behavior.
- Display a welcome message when the workbook opens.
- Activate a Dashboard worksheet when the workbook opens.
- Prevent saving if a required report title is missing.
- Display a confirmation message before closing the workbook.
- Validate a worksheet before printing.
- Display a message when the workbook becomes active.
- Format a new worksheet using the
Workbook_NewSheetevent. - Call a reusable Sub procedure from
Workbook_Open. - Temporarily disable events while updating a report and restore them safely.
- Test how workbook events behave when events are disabled.
Important Points to Remember
- Workbook events respond automatically to workbook actions.
- Write workbook event procedures inside
ThisWorkbook. Workbook_Openruns when the workbook opens with VBA events enabled.Workbook_BeforeSavecan validate data before saving.Workbook_BeforeClosecan cancel a closing operation.Cancel = Trueprevents the relevant operation when supported by that event.Application.EnableEventscontrols Excel event handling.- Restore application settings safely after temporarily changing them.
- Test event procedures with trusted macro-enabled workbooks.
Summary
Workbook events allow VBA code to respond to workbook actions such as opening, saving, closing, printing, activation, and creating new sheets. They are useful for initialization, validation, confirmation dialogs, and repeatable workbook setup.
Place event procedures in the ThisWorkbook module,
use the appropriate event signature, validate worksheet references,
and restore application settings after temporarily disabling
events. The next topic covers worksheet-level events that respond
to changes and selections on individual sheets.