Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

Trapping xl Application Events by JohnAcc
Posted: 19 Sep 03

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

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close