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

Understanding events. II: Automation events by combo
Posted: 23 Mar 04

The whole FAQ consists of four parts
the basics
automation events (this one)
some useful stuff
a substitute for control arrays in VBA

If we consider excel, an event server can be a workbook, worksheet, , application etc. MSForms supply events for userform and controls. It is easy to handle event procedures for workbook, just go to ThisWorkbook module, select ôWorkbookö from the left list and get the event from the right one. It looks like there would be a hidden declaration Private WithEvents Workbook As Workbook and Set Workbook=Me somewhere.
It is not so easy with application and some other objects. A way suggested by MS help file is
(1) create a class handling application events
(2) create an instance of this class and assign Application to its WithEvents variable
In practice it looks like this:
A class module clsApp:

Public WithEvents App As Application

æ procedure available due to above declaration
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
æ event procedure code
End Sub

A standard module:
Dim X as New clsApp

Sub Init()
Set X.App=Application
End Sub

The rules are still the same: Init procedure instantiates clsApp class (X object created) and assigns Application to its App variable. We still have object-object event transfer.
Just a remark: donÆt write unknown event handling procedure names yourself, let VBA do it for you. Sometimes you canÆt trap all events using WithEvents. For instance, after Public WithEvents cmd As MSForms.TextBox, you canÆt get Private Sub cmd_Enter(), otherwise easily available after naming TextBox as cmd and double-click it. The reason is that a part of TextBox object is defined by MSForms.TextBox, the other part, including Enter event, by its container, MSForms.Control. It can be checked in the Object Browser in MSForms library.

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