Worksheet and workbook level VBA events

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Application level events in Excel VBA Part one of a three-part series of blogs
In Excel VBA it's pretty easy to create event procedures which apply to a single worksheet or workbook. But what if you want an event to work in every workbook you open? This blog explains how to create application level event procedures in Excel VBA. You'll learn how to create a class module, how to declare application variables which support events and how to enable and disable application level events. Worksheet and workbook level VBA events (this blog) Application level events in Excel VBA Activating application level events automatically

Application level events in Excel VBA

Part one of a three-part series of blogs

In Excel VBA it's pretty easy to create event procedures which apply to a single worksheet or workbook. But what if you want an event to work in every workbook you open? This blog explains how to create application level event procedures in Excel VBA. You'll learn how to create a class module, how to declare application variables which support events and how to enable and disable application level events.

Worksheet and workbook level VBA events (this blog)
Application level events in Excel VBA
Activating application level events automatically

Posted by Andrew Gould on 01 August 2024

In this blog

Creating a Worksheet Level Event Procedure
Creating a Workbook Level Event Procedure
The Limitation

In Excel VBA it's fairly easy to create events which work on a single worksheet or workbook. Let's say we want to use an event procedure to change the width of a column as we enter data into cells:

Automatically changing column widths in Excel

We want the column to change its width automatically as we enter data.

Creating a Worksheet Level Event Procedure

We can make this work on a single worksheet quite easily. In the Visual Basic Editor, double-click the worksheet whose events you want to access:

Selecting a worksheet in the Excel Visual Basic Editor

Double-click the worksheet in the Project Explorer window.

Use the drop down lists at the top of the code window to select the relevant object and event:

Selecting an event procedure in a sheet module in Excel VBA

Select Worksheet from the drop down list on the left, and Change from the list on the right.

When you select Worksheet from the drop down list on the left, VBA will insert the default worksheet event called Worksheet_SelectionChange. After you have selected the Change event, you can delete the default event.

Add code to the event procedure to perform whatever actions you want. The example code shown below will change the width of the column to match its widest entry:

Private Sub Worksheet_Change(ByVal Target As Range)

Target.EntireColumn.AutoFit

End Sub

This code will now run automatically each time you change the value of a cell on the selected worksheet.

Creating a Workbook Level Event Procedure

If you want the same event to work on every worksheet in the workbook, you can instead create a workbook level event procedure. To do this, start by double-clicking the ThisWorkbook object in the Visual Basic Editor:

Selecting this workbook in the Excel VIsual Basic Editor

Double-click ThisWorkbook in the Project Explorer window.

Use the drop down lists at the top of the code window to select the Workbook object and the SheetChange event:

Selecting a workbook event procedure in Excel VBA

Choose Workbook from the left-hand drop list and SheetChange from the right-hand drop list.

Add the same code to the new event, as shown below:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Target.EntireColumn.AutoFit

End Sub

This code will run automatically when you change the value of a cell on any worksheet in the workbook.

The Limitation

The only problem with the example shown above is that it will only work in the workbook that you've added the code to. The next part of this blog shows you how to make it work for any workbook!

Parts of this blog
Worksheet and workbook level VBA events (this blog) Application level events in Excel VBA Activating application level events automatically

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Creating a Worksheet Level Event Procedure

Creating a Workbook Level Event Procedure

The Limitation

Head office

London

Manchester