Activating application level events automatically

This page has 1 thread | Add post

Blogs

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

Application level events in Excel VBA Part three 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 Application level events in Excel VBA Activating application level events automatically (this blog)

Application level events in Excel VBA

Part three 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
Application level events in Excel VBA
Activating application level events automatically (this blog)

Posted by Andrew Gould on 01 August 2024

In this blog

Creating the Personal Macro Workbook
Copying Code into the Personal Macro Workbook
Using the Workbook Open Event
Saving the Personal Macro Workbook
Testing Your Code

One easy way to get code to run automatically whenever you open Excel is to use the Workbook_Open event of the Personal Macro Workbook. The Personal Macro Workbook is a hidden workbook stored as part of your user profile.

The XLSTART folder and the personal macro workbook.

When you've created the Personal Macro Workbook, you'll find it in the XLSTART folder.

Whenever you open Excel it checks if the Personal Macro Workbook exists and, if so, opens it automatically as a hidden workbook. This means that any code you store in it will be available whenever you open Excel.

Creating the Personal Macro Workbook

Perhaps the easiest way to start creating the Personal Macro Workbook is to choose to record a macro. You can see one way to begin recording a macro in the image below:

Click this button in the bottom left corner of the Excel window to begin recording a macro.

On the dialog box which appears, choose to store the macro in the Personal Macro Workbook.

Choosing where to save a recorded macro in Excel.

Choose the option shown to store the macro in the Personal Macro Workbook.

When you click OK the macro begins recording. Your macro doesn't need to do anything, so you can immediately stop recording as shown in the image below:

Stopping a recording macro in Microsoft Excel

Click the square in the bottom left corner of the Excel window to stop recording.

When you return to the Visual Basic Editor you should see the Personal Macro Workbook in the Project Explorer window.

The Personal Macro Workbook in the Visual Basic Editor in Excel

The PERSONAL.XLSB workbook is visible in the Visual Basic Editor but not in Excel.

You can delete the module from the Personal Macro Workbook as you won't need it. You can do this as shown in the image below:

Deleting a module in the Excel Visual Basic Editor

Right click on the module and choose to remove it. Click No on the dialog box which appears.

Copying Code into the Personal Macro Workbook

Now you can copy the class module and normal module from the other workbook into the Personal Macro Workbook. The easiest way to do this is simply to click and drag the modules from one project to the other in the Project Explorer window as shown below:

Copying modules between projects in Excel VBA

Click and drag a module from one project to another.

Using the Workbook Open Event

Now you can add code to the Workbook_Open event of the Personal Macro Workbook. You can do this in the same way as for any other workbook. Start by double-clicking the ThisWorkbook object.

Selecting the ThisWorkbook object in an Excel VBA project

Double-click the ThisWorkbook object to open its code window.

Select the Workbook object from the drop down list at the top left of the code window.

Selecting the workbook object in Excel VBA

Choosing Workbook adds the Open event automatically.

In the Workbook_Open event, add an instruction to call the Activate_App_Level_Events subroutine from Module1.

Private Sub Workbook_Open()

Activate_App_Level_Events

End Sub

The code shown above runs automatically each time you open Excel.

Saving the Personal Macro Workbook

To save the Personal Macro Workbook, select any item which belongs to it in the Project Explorer window and then click the Save button as shown below:

Click any item in the PERSONAL.XLSB project then click the button shown to save it.

You can now close Excel without saving any changes to the other open workbook.

Testing Your Code

To test that your code works, all you need to do is open a fresh copy of Excel and create a new workbook. You should find that your events work automatically without any further effort from you!

Testing application level events in Microsoft Excel

The final result of your hard work!

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

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	James Martin
When:	16 Oct 24 at 13:40

This is a brilliant blog. Been looking for a workable App Event Level Event Trigger for a while. The code on other sites was not working. Looking to automatically trigger a few different events including A "Macro L" (saved on LinkedIn) I have to log all active window changes I have on a performance monitoring Sheet. On the Freeze Pane it records performance stats including Lock Screen breaks. It also records Macro uses, and I have been getting 2.5 hours of time savings with 2,500 Active Windows in a standard shift day, for a previous repetitive role.

From:	Andrew G
When:	17 Oct 24 at 07:29

Thanks James, happy to hear that you found it useful!

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Creating the Personal Macro Workbook

Copying Code into the Personal Macro Workbook

Using the Workbook Open Event

Saving the Personal Macro Workbook

Testing Your Code

Head office

London

Manchester