Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Understanding events. II: Automation events

VBA How To

Understanding events. II: Automation events

by  combo  Posted    (Edited  )
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:

[tt][color blue]Public WithEvents App As Application

æ procedure available due to above declaration
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
æ event procedure code
End Sub[/color][/tt]
A standard module:
[tt][color blue]Dim X as New clsApp

Sub Init()
Set X.App=Application
End Sub[/color][/tt]

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.
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top