Application Level

Application level events are built-in but are not easily accessible.
There are 57 different Excel > Application Level Events
You must write your own event handler routines when you want to capture any of the Application events.
This can be done using the WithEvents keyword in a class module.
Create a new class module called "ApplicationEventClass"

microsoft excel docs

Declare an object of type Application and define this variable to include "WithEvents"

Public WithEvents ExcelAppEvents As Application 

After the new object has been declared with events, it appears in the Object box in the class module (top left).
When you select the new object in the Object box, the valid events for that object are listed in the Procedure box (top right).

microsoft excel docs

However before the event handlers will run you must connect the declared object in the class module to the Application object.
You can do this from a standard module
Create a new standard module called "Module1"

microsoft excel docs

Declare an object of type "ApplicationEventClass"

Public ApplicationClass As New ApplicationEventClass 

Remembering that "ApplicationEventClass" is the name of the class module you created above.

After you have created the object variable you can set the ExcelAppEvents object of the EventClass to equal the Microsoft Excel application object.

Sub ConnectEventHandler 
   Set ApplicationClass.ExcelAppEvents = Application
nd Sub
microsoft excel docs

Once this has been assigned the events in the EventsClass will run whenever the events occur.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext