In a previous post I wrote about the sequence of events in workbooks. This post is about the sequence of events in a simple userform. To built a reliable userform you need to know something about the userform mysteries.
A simple userform
Userform1 in the attached workbook is a simple userform. Features of this form are:
- three TextBox controls, accessible by accelerator keys;
- button Clear to Clear the textboxes; property Cancel set (Esc key), accelerator key set to Alt-r;
- button Unload to unload the form; accelerator key set to Alt-u;
- button Hide to Hide the form; property Default set (Enter key).
In the code module of the form the most relevant events are defined. Most of these event handlers contain only two lines of code to create a trace list in the VBE Immediate Window. See Debug and trace in VBA for details on this debug technique.
Loading and showing the form
Loading the form raises only one event:
Showing the form from an unloaded state results in:
UserForm_Initialize TextBoxA_Enter UserForm_Activate
The second event only fires if the form contains a control that can take the focus and is enabled to do so.
Notice the difference with the workbook event model, where activation is top-down: Workbook – Window – Worksheet. The Forms object library seems to do the opposite: first activate/enter the low level control, then activate the form.
Unloading the form
Quote from the Visual Basic Language Reference: “The Deactivate event occurs when an object is no longer the active window”. So, if we unload or hide the form we would expect a deactivate event to be raised. Unfortunately this is not so. Click the window Close button immediately after startup and the trace list results in:
UserForm_QueryClose TextBoxA_Exit UserForm_Terminate
Indeed, there is no Deactivate event fired.
If we close the form with a click on the Unload button the trace list becomes:
TextBoxA_Exit ButtonUnload_Enter ButtonUnload_Click UserForm_QueryClose ButtonUnload_Exit UserForm_Terminate
The Unload Me command in the ButtonUnload_Click handler fires the QueryClose and Terminate events. Except for the missing deactivate no surprises.
Hiding and re-Showing the Form: deactivate and activate
A click on the Hide button fires one event:
Again, the deactivate event is missing.
Showing the form again also fires one event:
As expected. In general:
- the UserForm_Activate event is very useful in case you need to do some initialization each time the userform returns from an idle state (in contrast to only once initialization in the Userform_Initialize handler);
- unfortunately, the UserForm_Deactivate event is in simple userforms of no use at all. This event is only raised when you switch to another userform, either from a modal form to another modal child form, or from a modeless form to another modeless form. In case you need to do some finalization each time the form deactivates you need to define the exit-points yourself and perform the exit code “manually”.
With respect to the hide command: keep in mind that this ends the modal state of the userform, and that code execution continues after the show command.
When you switch from TextBoxA to TextBoxB by a mouse click these events are fired:
TextBoxA_Exit TextBoxB_Enter TextBoxB_MouseDown TextBoxB_MouseUp
Note that the TextBox control has no Click event.
Type something in TextboxA and then click on TextBox B:
TextBoxA_BeforeUpdate TextBoxA_AfterUpdate TextBoxA_Exit TextBoxB_Enter TextBoxB_MouseDown TextBoxB_MouseUp
No surprises. Keep the troika BeforeUpdate – AfterUpdate – Exit in mind. From the MSForms help: “The BeforeUpdate event occurs before the AfterUpdate and Exit events for the control and before the Enter event for the next control that receives focus”. We shall see that this is not always the case.
Clear the Form
Type some text in Textboxes A and C and set the focus on Textbox B. Then a click on button Clear results in:
TextBoxB_Exit ButtonClear_Enter ButtonClear_Click TextBoxA_Change TextBoxC_Change
Note that TextBoxB wàs modified by the event handler (set to vbnullstring), however the Change event was not raised. Fortunately MSForms checks for a real value change, saving us work.
Side note: when you click a button its default behaviour is to take the focus. We can prevent this by setting the TakeFocusOnClick property to false and keep the focus on the textbox. This could be a nice feature for the Clear button, for example. Personally I prefer the default behaviour, which is the default in most windows dialogues. Note that the keyboard interface ignores the TakeFocus setting. For example, if you press Escape the button always takes the focus.
Debugging the userform – mystery one
Suppose you want to debug the above event series. So, you set a breakpoint in the TextBoxB_Exit event handler and click the Clear button. Code execution is suspended at the breakpoint. Now, in debug mode just press F5 to continue. The trace log results in:
Note the differences with the list above. We clicked the Clear button but the ButtonClear_Click event was not raised (or should we say: was not processed), and indeed, textboxes A and C were not cleared. Let’s call this “The mystery of the missing click event”.
Keep in mind that in debug mode not everything is what it seems to be. Personally, when debugging event rich userforms I find my trace utility indispensable.
Setting the focus – mystery two
Suppose that after clearing the form you want the user to start in TextBox A. So you add the line
at the end of the event handler for button Clear. This is the trace log with all textboxes empty and the focus already on textbox A:
TextBoxA_Exit ButtonClear_Enter ButtonClear_Click ButtonClear_Exit TextBoxA_Enter
In the ButtonClear_Click handler the focus is set to textbox A, resulting in the two indented events. No surprises.
Now type some text in textbox A and click Clear:
TextBoxA_BeforeUpdate TextBoxA_AfterUpdate TextBoxA_Exit ButtonClear_Enter ButtonClear_Click TextBoxA_Change ButtonClear_Exit TextBoxA_Enter TextBoxA_BeforeUpdate TextBoxA_AfterUpdate
Where do the last two Update events come from??? And where is the Exit event that always should be part of this troika? I don’t have answers but this is very annoying. Suppose you want to log every change a user makes, and you decide to use the AfterUpdate event for this – makes perfectly sense. Then, in your log, you find that sometimes a TextBoxA entry is duplicated.
Let’s call this “The mystery of the Setfocus bonus events”.
So be careful with Setfocus. Solve the mystery by simply taking away the Setfocus command. Or take special care if you decide to use the Before/AfterUpdate events together with the Setfocus method. For example by disabling events temporarily, like this:
- in the ButtonClear_Click handler: set a global switch to true;
- in the BeforeUpdate handler: if this switch is true then exit the sub;
- in the AfterUpdate handler: if this switch is true then set the switch to false and exit the sub.
Keyboard events: textboxes
Just type an ‘a’ in textbox A:
TextBoxA_KeyDown 65 TextBoxA_KeyPress a TextBoxA_Change TextBoxA_KeyUp 65
No surprises. It seems obvious that a KeyDown must always be followed by a KeyUp. However, we will see later that this is not always true.
Using the keyboard you can switch from TextBoxA to TextBoxB by pressing the tab-key:
TextBoxA_KeyDown 9 TextBoxA_Exit TextBoxB_Enter TextBoxB_KeyUp 9
Again no surprises. Key 9 is the Tab key.
You can also switch from A to B by pressing alt-b:
TextBoxA_KeyDown 18 TextBoxA_KeyDown 66 TextBoxA_Exit TextBoxB_Enter TextBoxB_KeyUp 66 TextBoxB_KeyUp 18
No surprises either. Key 18 is the Alt-key.
Keyboard events: buttons – mystery three
Using the keyboard you can activate the Clear button in three ways: press Esc; press Alt-r; or tab to the control and press Enter or Space.
In the next cases start by typing some text in textbox A and set the focus on textbox B. Make sure that the command TextBoxA.SetFocus is commented out again.
TextBoxB_KeyDown 27 TextBoxB_Exit ButtonClear_Enter ButtonClear_Click TextBoxA_Change
TextBoxB_KeyDown 18 TextBoxB_KeyDown 82 TextBoxB_Exit ButtonClear_Enter ButtonClear_Click TextBoxA_Change
Tab from TextBox B to button Clear and then press Space:
TextBoxB_KeyDown 9 TextBoxB_Exit TextBoxC_Enter TextBoxC_KeyUp 9 TextBoxC_KeyDown 9 TextBoxC_Exit ButtonClear_Enter ButtonClear_Click TextBoxA_Change
Note that in all three cases the KeyDown events all miss there KeyUp counterpart. Mystery number three: “the mystery of the missing key events”.
Again, I don’t have answers. But again setting the focus is involved, although this is now done by MSForms. Coincidence? And is it a coincidence that the SetFocus method is handled by the ActiveX control container? Is there another cause?
If you have any answers, please drop a line. Having no answers is so annoying…
As with workbooks and worksheets it is possible to trap MSForms events. That is, we can trap the events of the native controls. It is not possible to trap the events that are handled by the ActiveX control containers:
- Initialize, Activate, Deactivate, Resize, QueryClose and Terminate events of the Userform cannot be trapped;
- Afterupdate, BeforeUpdate, Enter and Exit events of the other controls cannot be trapped.
UserForm2 in the attached workbook is a copy of UserForm1. I added four classes: CButtonEvents, CControlEvents, CTextBoxEvents and CUserformEvents. CControlEvents was added for research only and cannot be used. In the Userform_Initialize handler objects are created to trap the events of the userform, the three textboxes and the three buttons. The UserForm_Terminate handler tidies up these objects. Some examples of the trace list created with UserForm2 are presented below.
Example 1: show the form from unloaded state:
UserForm_Initialize Class_Initialize UserForm Class_Initialize TextBox Class_Initialize TextBox Class_Initialize TextBox Class_Initialize Button Class_Initialize Button Class_Initialize Button TextBoxA_Enter UserForm_Activate
No trapped events, since the last two events cannot be trapped.
Example 2: type an ‘a’ in textboxA:
TextBoxA_Change TrappedTextbox_Change TextBoxA
Note that the trapped event is processed after the native event, in contrast to trapped events in workbooks and worksheets.
Example 3: with the focus on textbox A click on button Hide:
TextBoxA_Exit ButtonHide_Enter ButtonHide_Click TrappedButton_Click ButtonHide
Example 4: with the focus on textbox A a click on button Close
TextBoxA_Exit ButtonClose_Enter ButtonClose_Click UserForm_QueryClose 1 ButtonClose_Exit UserForm_Terminate Class_Terminate UserForm Class_Terminate TextBoxA Class_Terminate TextBoxB Class_Terminate TextBoxC Class_Terminate ButtonClose Class_Terminate ButtonClear Class_Terminate ButtonHide
Note that the TrappedButton_Click event for button Close was not fired. Or perhaps we should say: it was fired but not processed, since the userform was terminated at that point.
Developing a robust userform is not as straightforward as it seems. Be aware of the userform mysteries, and keep things simple.
I hope this pixcel helps. Excel!