Userform Events

Events are actions that can be recognized by an object. The UserForm object can recognize certain events such as Userform initialization upon opening, activation (and deactivation), closing, click/double click and other mouse events, keystroke events, etc. We can get or set the value of certain properties or perform actions as a response to such events with VBA code in the respective event procedures.

 

List of events

The events listed below are triggered when showing and closing a Userform. They occur in the order given below. The event Initialize happens before the Userform appears, followed by Activate. A Userform is activated when shown or when clicking on the form if another Userform was active before (in case there are 2 forms or more). Similarly, a Userform is deactivated when activating another form while keeping the first one open. The events QueryClose and Terminate are triggered when closing the form. The difference between QueryClose and Terminate is that QueryClose is triggered before the Userform is removed from memory while Terminate occurs after released from memory (fully closed).

• Initialize
• Activate
• (Deactivate)
• QueryClose
• Terminate

QueryClose allows to control certain aspects when closing the Userform. As we will see later, an event procedure for a particular event can accept parameters. QueryClose accepts two parameters: Cancel and CloseMode. Setting “Cancel” to True prevents from closing the Userform. “CloseMode” returns a number that identifies the closing action. There are four ways altogether to close a Userform: with the red cross close button at the top-right of the form (value 0), with Unload (value 1), closing the parent application (value 2), and from the task manager (value 3). We can disable a certain way of closing the form with CloseMode along Cancel. For example, we can prevent from closing the form with the red cross button with the code in the event procedure below.

 Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
     If CloseMode = 0 Then Cancel = True
 End Sub

There are a number of mouse-related events. We can react to a click or a double click on the Userform (Click and DblClick events). We can also capture the mouse move or mouse down and up events. The order in which events happen follows the sequence shown below.

Mouse left click: MouseDown, MouseUp, Click

Mouse right click: MouseDown, MouseUp (there is no right click event)

Mouse double click: MouseDown, MouseUp, Click, DblClick, MouseUp

 

Similarly, there are also key-related events, which recognize pressing a key in the keyboard while the Userform is open. There are three key-related events: KeyDown, KeyPress, KeyUp, and they happen in that particular order. These events are rather used with certain controls, for example to react to certain keys (or characters) when entering data in a text or combo box (e.g. avoid entering special characters, avoid pressing delete or other keys), because many of the Userform events also apply to Userform controls (see next chapter for controls).

And there are several other Userform events that may be useful in some cases. We can recognize for example the event of adding (or removing) a control, when controls are added (or removed) programmatically. As mentioned earlier, Userform controls have many events in common with the Userform, and many events are often managed at control level. But we will look at that in the next chapter.

 

Event procedures

The code to respond to an event is stored in the corresponding event procedure. We can see the list of events and add an event procedure from the drop-down on the top-right of the code window when selecting Userform in the drop-down on the left.

We can add VBA code to the procedure to respond to a given event. For example, we can use the Initialize event procedure to set certain properties of the form and its controls such as populating a combo box or adding items to a list box. We will see how to do that later in this training. Here’s an example of the code to update the caption and set to modeless before is shown.

 Private Sub UserForm_Initialize()
     ShowModal = False
     Caption = "Registration Form"
 End Sub

Note that the properties in the example above can also be set from within the calling procedure. But we may want to keep the code in the Userform module when planning to import/export the Userform to/from other VBA project.

We can prompt a message or call a macro to do something else after closing the Userform with QueryClose or Terminate.  We have explained the difference between the two events earlier in this page.

 Private Sub UserForm_Terminate()
     MsgBox "Registration complete"
     Call SaveDataToSheet
 End Sub

And as just mentioned above, that code can also be added to the calling procedure that is closing the form. The only reason we may want to keep it here is in case it’s some standard code to be always run when closing a form or when we want to keep it in the Userform module to later export and reuse the form and code. Thus, we can have standard templates with default actions that we want to have in all the Userforms. As an example, we could want to always have the close form button disabled and warn the user if trying to close the form from there (instead of using a command button in the form, for example).

 Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
     If CloseMode = 0 Then
         Cancel = True
         MsgBox “Use the buttons below to save, submit, or close the form”
     End If
 End Sub

As shown in the example just above, some event procedures accept parameters. QueryClose accepts the parameters Cancel and CloseMode explained earlier. Key-related events accept a parameter that identifies the key code or ASCI code associated with a key. We can use that parameter to perform some action in the event of pressing a certain key in the keyboard. For example, we can identify at the moment of putting the key down if it is the “Return” key, the “Delete” key, and many other (see Keycode constants). 

 Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
     If KeyCode = vbKeyReturn Then
         'do something, maybe bring the form to the center of the screen
         Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)

         Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)  
     ElseIf KeyCode = vbKeyDelete Then
         'do something else
     End If
 End Sub

 

We can also identify characters with the KeyAscii parameter of the KeyPress event. KeyAscii is the ASCI code of the character, which can also be one of the control characters. The example below would close the form if “ESC” is pressed (the ASCI control code for ESC is 27 - note that we could also do that with KeyDown along vbKeyEscape). See the full list of ASCI codes in this other page.

 Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     If KeyAscii = 27 Then Unload Me
 End Sub

However, both KeyDown and KeyPress won’t work if the Userform has controls such as a text or combo box or also a button, among others, as those depend on key events to input data (or be clicked), unless the property TabStop of the controls is set to False. But, as explained earlier, KeyPress and also KeyDown are more often used with Userform controls such as text or combo boxes to avoid entering special characters or pressing certain control keys or even allowing only to enter numbers, etc, thus allowing to control the input and avoid errors. We will see more about that in the next chapter.

Some mouse-related event procedures also accept parameters, for example to store the horizontal and vertical position of the mouse cursor. Event procedures for adding and removing controls can identify the type of control being added/removed. Procedures for error events have parameters for error code, source of error, etc.

But now is time to start adding controls to the Userform. In the next sections we will see how to build a functional form with Userform controls, work with the controls to display and gather information or execute certain actions.

<<< Previous | Next >>>