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.
Talk To Other Members