Trapping xl Application Events

Hi there:

By trapping an application's events you can write code that will be run each time your specified event is triggered in each instance of excel. The benefit of this is that you can access excel's events as opposed to specific worksheet or workbook events.

To demonstrate this I will use a simple example of showing a msgbox each time a workbook is opened.

To achieve this you need to create a class module in personal (called ClsAppEvents) and type the following code:

Public WithEvents xlapp as Application

Public Sub xlapp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox " Testing Trap Events! "
End Sub


Then create a standard module in personal (called TrapAppEvents) and type the following code:

Public xlApplication as new ClsAppEvents

Public Sub TrapApplicationEvents()
Set xlApplication.xlapp = Application
End Sub


Now each time you open a new workbook your code will be executed. The above is a simple example to explain the use of the class module and can be altered to trap any of the xl events.

To view all of the excel events that are accesible go into the class module and select xlapp from the left hand side drop down menu and then drop down the right hand menu.

Hope you find this as useful as i have.

Rgds, John


Note: If you are unsure how to re-name a module then left click on the module and select view > properties window from the toolbar