You must write your own event handler routines when you want to capture any of the Application events since these events are not enabled by default.
This can be done using the WithEvents keyword in a class module.
Create a new class module called "ApplicationEventClass"
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).
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"
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.
Set ApplicationClass.ExcelAppEvents = Application
Once this has been assigned the events in the EventsClass will run whenever the events occur.
© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited