Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

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.

Workbook Action Event Occurs VBA Event Procedure Execute the instructions automatically
Key concept: An event procedure runs in response to an event. It is different from an ordinary macro that you explicitly run from the Macro dialog or VBA editor.

2. Where Do Workbook Events Belong?

Workbook event procedures belong in the ThisWorkbook code module, not in an ordinary standard module.

  1. Open Excel and press Alt + F11.
  2. Locate your workbook in Project Explorer.
  3. Expand the Microsoft Excel Objects folder.
  4. Double-click ThisWorkbook.
  5. Choose Workbook from the left object dropdown.
  6. Select the required event from the right procedure dropdown.
  7. Write your code inside the generated event procedure.
VBAProject (SalesReport.xlsm) ▾ Microsoft Excel Objects Sheet1 (Sales) ThisWorkbook ← Workbook events here ▾ Modules Module1 — ordinary macros and reusable procedures
Important: A workbook event written in a standard module will not automatically run as a workbook event. Place the event procedure in 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
Note: The workbook must contain a worksheet named 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

  1. Excel starts the save operation.
  2. The event checks cell B2 on the Dashboard worksheet.
  3. If B2 is blank, a warning appears.
  4. Setting Cancel = True cancels the save operation.
  5. 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
Behavior: Choosing No cancels the close operation. Choosing Yes allows Excel to continue closing the workbook, subject to any other prompts or events.

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
Important: The status bar belongs to the Excel application, not only to this workbook. If you change it, consider restoring the previous state appropriately so you do not unintentionally affect the user's Excel session.

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
Critical: Always restore 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

  1. Save your workbook as an Excel Macro-Enabled Workbook (.xlsm).
  2. Open the VBA editor with Alt + F11.
  3. Double-click ThisWorkbook.
  4. Write a small event procedure such as Workbook_Open.
  5. Save the workbook and close it.
  6. Reopen the workbook in desktop Excel.
  7. Enable macros only if the workbook and its source are trusted.
  8. Verify that the expected event procedure runs.
Testing tip: To test a save event, save the workbook after making a change. To test a close event, close the workbook and respond to any confirmation dialog that appears.

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.EnableEvents set 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.

  1. Display a welcome message when the workbook opens.
  2. Activate a Dashboard worksheet when the workbook opens.
  3. Prevent saving if a required report title is missing.
  4. Display a confirmation message before closing the workbook.
  5. Validate a worksheet before printing.
  6. Display a message when the workbook becomes active.
  7. Format a new worksheet using the Workbook_NewSheet event.
  8. Call a reusable Sub procedure from Workbook_Open.
  9. Temporarily disable events while updating a report and restore them safely.
  10. 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_Open runs when the workbook opens with VBA events enabled.
  • Workbook_BeforeSave can validate data before saving.
  • Workbook_BeforeClose can cancel a closing operation.
  • Cancel = True prevents the relevant operation when supported by that event.
  • Application.EnableEvents controls 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.