Sequence of events in Userforms Pixcels. 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. Userform. 1 in the attached workbook is a simple userform. Features of this form are three Text. Box 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. User. FormInitialize. Clear The Immediate Window Vba Download WindowsShowing the form from an unloaded state results in. User. FormInitialize. User. FormActivate. The second event only fires if the form contains a control that can take the focus and is enabled to do so. 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. Show All Records. The following Excel AutoFilter VBA code shows all records, if a filter has been applied. Sub ShowAllRecords If ActiveSheet. Runtime Error In Vba Fix, Clean RUNTIME ERROR IN VBA And Optimize PC SPEED Up Your PC FREE Scan Now Recommended. How To Fix Runtime Error 53. 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 activateenter 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. User. FormQuery. Close. User. FormTerminate. Indeed, there is no Deactivate event fired. If we close the form with a click on the Unload button the trace list becomes. Button. UnloadEnter. Button. UnloadClick. User. FormQuery. Close. Button. UnloadExit. User. FormTerminate. Clear The Immediate Window Vba Download PcFor all general questions relating to Excel but not including VBA or formulas. The Unload Me command in the Button. UnloadClick handler fires the Query. Close 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. Button. HideClick. Again, the deactivate event is missing. Showing the form again also fires one event. User. FormActivate. As expected. In general the User. FormActivate 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 UserformInitialize handler unfortunately, the User. FormDeactivate 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 Text. Box. A to Text. Box. B by a mouse click these events are fired. Text. Box. BMouse. Down. Text. Box. BMouse. Up. Note that the Text. Box control has no Click event. Type something in Textbox. A and then click on Text. Box B. Text. Box. ABefore. Update. Text. Box. AAfter. Update. Text. Box. BMouse. Down. Text. Box. BMouse. Up. No surprises. Keep the troika Before. Update After. Update Exit in mind. From the MSForms help The Before. Update event occurs before the After. Update 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. Button. ClearEnter. Button. ClearClick. Text. Box. AChange. Text. Box. CChange. Note that Text. Box. B ws 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 Take. Focus. On. Click 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 Take. Focus 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 Text. Box. BExit 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. Button. ClearEnter. Note the differences with the list above. We clicked the Clear button but the Button. ClearClick event was not raised or should we say was not processed, and indeed, textboxes A and C were not cleared. Lets 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 Text. Box A. So you add the line. Me. Text. Box. A. Set. Focus. 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. Button. ClearEnter. Button. ClearClick. Button. ClearExit. Text. Box. AEnter. In the Button. ClearClick 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. Text. Box. ABefore. Update. Text. Box. AAfter. Update. Button. ClearEnter. Button. ClearClick. Text. Box. AChange. Button. ClearExit. Text. Box. AEnter. Text. Box. ABefore. Update. Text. Box. AAfter. Update. Where do the last two Update events come from And where is the Exit event that always should be part of this troika I dont have answers but this is very annoying. Suppose you want to log every change a user makes, and you decide to use the After. Update event for this makes perfectly sense. Then, in your log, you find that sometimes a Text. Box. A entry is duplicated. Lets 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 BeforeAfter. Update events together with the Setfocus method. For example by disabling events temporarily, like this in the Button. ClearClick handler set a global switch to true in the Before. Update handler if this switch is true then exit the sub in the After. Update handler if this switch is true then set the switch to false and exit the sub. Indeed, pffffKeyboard events textboxes. Just type an a in textbox A. Text. Box. AKey. Down 6. 5. Text. Box. AKey. Press a. Text. Box. AChange. Text. Box. AKey. Up 6. No surprises. It seems obvious that a Key. Down must always be followed by a Key. Up. However, we will see later that this is not always true. Using the keyboard you can switch from Text. Box. A to Text. Box. B by pressing the tab key. Text. Box. AKey. Down 9. Text. Box. BKey. Up 9. Again no surprises. Key 9 is the Tab key. You can also switch from A to B by pressing alt b. Text. Box. AKey. Down 1. 8. Text. Box. AKey. Down 6. 6. Text. Box. BKey. Up 6. 6. Text. Box. BKey. Up 1. 8. No surprises either. Key 1. 8 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 Text. Box. A. Set. Focus is commented out again. Press Escape. Text. Box. BKey. Down 2. Button. ClearEnter. Button. ClearClick. Text. Box. AChange. Text. Box. BKey. Down 1. 8. Text. Box. BKey. Down 8. 2. Button. ClearEnter. Button. ClearClick. Text. Box. AChange. Tab from Text. Box B to button Clear and then press Space. Text. Box. BKey. Down 9. Text. Box.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |