Sequence of events in Userforms

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).

The form Close button and Alt-F4 also unload the form.

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:

UserForm_Initialize

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:

ButtonHide_Click

Again, the deactivate event is missing.
Showing the form again also fires one event:

UserForm_Activate

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.

Switching controls

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:

TextBoxB_Exit
ButtonClear_Enter

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

    Me.TextBoxA.SetFocus

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.

Indeed, pffff…

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.

Press Escape:

TextBoxB_KeyDown  27
TextBoxB_Exit
ButtonClear_Enter
ButtonClear_Click
    TextBoxA_Change

Press Alt-r:

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…

Trapping events

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.

Conclusion

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!

Download

SimpleUserFormEvents-v1.xlsm

See also

The sequence of events in workbooks