INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
"...At last there is indeed a website/forum that
deals with professional and serious matters. Keep up with the good work!!"
Where in the world do Tek-Tips members come from?
VBA How To
Understanding events. II: Automation events
Posted: 23 Mar 04
The whole FAQ consists of four parts
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
A standard module:
Dim X as New clsApp
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
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:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close