UserForm Events

 

An event is something happening at a point in time.  Visual Basic creates events to allow a time-static spreadsheet to handle real-world situations at the time they occur.  Events are of two types: those created by code working with objects, and those caused directly by user actions.

The UserForm itself only has three unique events, which often aren’t used.  In addition, there are events common to many objects.  UserForm events available:

UserForm_Activate (unique to UserForm)

UserForm_AddControl

UserForm_BeforeDragOver

UserForm_BeforeDropOrPaste

UserForm_Click

UserForm_DblClick

UserForm_Deactivate (unique to UserForm)

UserForm_Initialize

UserForm_KeyDown

UserForm_KeyPress

UserForm_Layout

UserForm_MouseDown

UserForm_MouseMove

UserForm_MouseUp

UserForm_QueryClose

UserForm_RemoveControl

UserForm_Resize

UserForm_Scroll

UserForm_Terminate (unique to UserForm)

UserForm_Zoom

As can be seen from the above list, very sophisticated applications can be written.  UserForm_Initialize is given the most attention in these lessons.  The other events will more likely be used for specific controls and not for the UserForm.  For example, there might be a use for double-clicking a ListBox, or even a Label, but it’s less like to have a use for double-clicking the UserForm itself.

Many events take arguments.  The best way to ensure the event handler has the correct format is to select the event from the Procedure drop-down above the code pane.  The handler will be created correctly, and the programmer can proceed from that point.

EXAMPLE

For UserForm_QueryClose(Cancel As Integer, CloseMode As Integer), the Cancel argument defaults to False.  Setting it to True prevents the UserForm from closing.  The system sets CloseMode according to what is attempting to close the UserForm, and CloseMode = 0 (vbFormControlMenu) is set when the user attempts to directly close the UserForm, such as by clicking the X in the top right corner of the UserForm.

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

This code would prevent the user from exiting the UserForm via the X.  Before implementing it, be sure to first create a Close button, and code the CloseButton_Click event to unload the UserForm.  If this is not done, the user will have no way to nicely close the UserForm.

To see the events available to UserForm or any of the controls, go to the UserForm object view and double-click the item of interest.  This will create a Click event handler in the Code view.  The complete list of Events can then be seen by clicking the drop-down arrow on the Procedure drop-down box on the upper right side, above the code pane.  This list of available events will be different for many of the controls.  A label, for example, will have many fewer events than a ComboBox.

UserForm Order of Events

The UserForm_Initialize event occurs first, after a Load statement.  If the Load occurs because a Show was used without a prior Load, then the Initialize event occurs after the UserForm is created, but before it is made visible.

The UserForm_Activate event occurs when the UserForm receives the focus, which is the same as becoming the active object.  This can occur as the second part of a show, when the UserForm was previously hidden, or when the worksheet or another UserForm has been the active object.

The UserForm_Deactivate event occurs when the UserForm loses the focus.

The UserForm_Terminate event occurs after the UserForm is unloaded.  If there are multiple instances of the UserForm, Terminate occurs only after the last instance unloads.  Terminate may not occur if the UserForm closes due to an error.

Control Events

User-driven events cluster into five types of activities: focus, mouse movement, mouse clicking, drag-and-drop, and keyboard use.  In addition, an event can signal an error.

Focus Change

The Enter event occurs before a control receives the focus from a control on the same form.

The Exit event occurs just before a control loses the focus to another control on the same form.

Mouse Movement

The MouseMove Event occurs when the user moves the mouse.

Mouse Clicking

The MouseDown event occurs when the user presses any mouse button.

The MouseUp occurs when the user releases any mouse button.

Drag And Drop

The BeforeDragOver event occurs when a drag-and-drop operation is in progress.  Use this event to monitor the mouse pointer as it enters, leaves, or rests directly over a valid target.  When a drag-and-drop operation is in progress, the system initiates this event when the user moves the mouse, or presses or releases the mouse button or buttons.  The mouse pointer position determines the target object that receives this event.  Determine the state of the mouse pointer by examining the DragState argument.  Use the Effect argument to identify the drag-and-drop action is to be Copy, Move, or None. 

The BeforeDropOrPaste event occurs when the user is about to drop or paste data onto an object.

Most controls do not support drag-and-drop while Cancel is False, which is the default setting.  This means the control rejects attempts to drag or drop anything on the control, and the control does not initiate the BeforeDropOrPaste event.  The TextBox and ComboBox controls are exceptions to this, since they support drag-and-drop operations even when Cancel is False.

Keyboard Keystrokes

The KeyPress event occurs when the user presses an ANSI key, and returns the ANSI key value.  ANSI codes typically cover printable characters and some special characters such as BACKSPACE, ESC. TAB, ENTER, DELETE, and arrow keys do not trigger KeyPress

The KeyDown event occurs when the user presses a key and the KeyUp event occurs when the user releases a key.  KeyDown and KeyUp identify what key was pressed, and which of SHIFT, CTRL, and/or ALT were pressed.  Special keys, such as the function keys, navigation keys, and keys on the numeric keypad, will trigger KeyDown and KeyUp.  These two events return a different code than KeyPress, and it can change depending on the type of physical keyboard in use.  Care must be taken if a macro is to work with keyboards of different countries.

The sequence of keyboard-related events is:

  1. KeyDown
  2. KeyPress
  3. KeyUp

When the user presses and holds a key, KeyDown and KeyPress alternate, at the keyboard repeat rate specified in the Control Panel Keyboard properties.  KeyUp occurs only once, when the key is released.

Error

The Error Event occurs when a control detects an error and cannot return the error information to a calling program.

ByVal References in Microsoft Forms  

The ByVal keyword in Microsoft Forms indicates that an argument is passed as a value; this is the standard meaning of ByVal in Visual Basic.  However, in Microsoft Forms, you can use ByVal with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString object.  When you do, the value passed is not a simple data type; it is a pointer to the object.

When used with these objects, ByVal refers to the object, not the method of passing parameters.  Each of the objects listed above has a Value property that you can set.  You can also pass that value into and out of a function.  Because you can change the values of the object's members, events produce results consistent with ByRef behavior, even though the event syntax says the parameter is ByVal.

Assigning a value to an argument associated with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString is no different from setting the value of any other argument.  For example, if the event syntax indicates a Cancel argument used with the ReturnBoolean object, the statement Cancel=True is still valid, just as it is with other data types.

 

Control

Short Description

Default Property

Default Event

Label

Displays fixed text

Caption

Click

TextBox

Displays entered text or for edit

Value

Change

ListBox

Displays a list of selectable items

Value

Click

ComboBox

Combines TextBox and ListBox.

Value

Change

CheckBox

For selecting any number of items

Value

Click

Frame

Used to group OptionButtons

(none)

Click

OptionButton

For selecting only one item

Value

Click

CommandButton

Click to execute code

Value

Click

ToggleButton

A two-state CommandButton

Value

Click

TabStrip

Displays tabs of the same limited info

SelectedItem

Change

MultiPage

Splits large amounts of info into pages

Value

Change

ScrollBar

Displays a vertical or horizontal scrollbar

Value

Change

SpinButton

Increments and decrements numbers

Value

Change

Image

Displays an image.

(none)

Click

 

Control Events

Each control on a UserForm has its own set of events.  The default event for most controls, as seen in the above table, is the Click event or the Change event.  The quickest and easiest way to find out the default event for a particular control is to place it on a UserForm and then simply double-click it.  Excel will create the default event procedure in the UserForm code module, and put the cursor in it ready for editing.

The order of events for a control depends on user movement of the mouse and user clicking of the left button.  Most of the time, it doesn’t matter, since the Click and Change events cover most needs, so order of events is not important.


EXAMPLE

On a UserForm place two TextBox controls.  Double-click each to create the Change event procedures, then place in each the code below. 

Private Sub TextBox1_Change()

    If TextBox1.Value = VBNullString then Exit Sub
    If Not IsNumeric(TextBox1.Value) Then
        MsgBox "No text please", vbCritical
        TextBox1.Text = vbNullString
    End If
End Sub

Private Sub TextBox2_Change()

    If TextBox2.Value = VBNullString then Exit Sub
    If IsNumeric(TextBox2.Value) Then
        MsgBox "No numbers please", vbCritical
        TextBox1.Text = vbNullString
    End If
End Sub

These Change event handlers ensure the user enters something, and correctly enters text or numbers.  For example, the user would be informed immediately if text is entered into TextBox1, or if a number is entered into Textbox2.  The Change event fires for each keystroke, which means the first character entered in Textbox2 cannot be a number, though numbers can be entered later and still have the contents register as text.

Note that the IF statement as the first line of code in the procedure above checks and exits if the TextBox is empty.  If this code was not in there, and a bad entry is reported, the code setting the TextBox to VBNullString will itself fire the Change event and cause a second error to be reported. 

Unfortunately, development and validation of UserForms is rarely this simple.  For the code below, intended for finding the @ in an email address, will not work in a Change event because the @ doesn’t occur until the middle of the address.

Private Sub TextBox3_Change()
    ' this code doesn’t work in a Change handler

    If TextBox3.Value = VBNullString then Exit Sub
    If InStr(1, TextBox3, "@", vbTextCompare) = 0 Then
        MsgBox "Not an email address, vbCritical"
    End If
End Sub

In cases like this, another event must run our validation check.  One event which can be used for this type of check is the Exit event.  Before looking at the Exit event though, it is important that the meaning of Focus is understood.  According to Microsoft Excel's VBE Help:

focus

The ability to receive mouse clicks or keyboard input at any one time.  In the Microsoft Windows environment, only one window, form, or control can have this ability at a time.  The object that "has the focus" is normally indicated by a highlighted caption or title bar.  The focus can be set by the user or by the application.

EXAMPLE

The Exit event will fire immediately before a control loses Focus to another control on the same form.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    ' this code now works in an Exit handler

    If TextBox3.Value = VBNullString then Exit Sub
    If InStr(1, TextBox3, "@", vbTextCompare) = 0 Then
        MsgBox "Not an email address", vbCritical
        Cancel = True
    End If
End Sub

A new argument in this handler is Cancel.  For this example, it is set to True when an error occurs.  Once a string is typed that includes @, it is easy to click in another control on the same UserForm and shift focus.  If the @ is not found, and the error is posted, Cancel = True cancels shifting the focus away from the control.  As the code is above, @ must be typed before focus is allowed to leave.  Rather than trap the user in an endless loop, the MsgBox message should tell the user why focus is unable to leave the control, and include what to do to get out.

There is a caveat, that if the control is in a Frame control, the Exit event doesn’t occur.

If this code is copied for a real project, be sure to change TextBox3 to the new TextBox name.

 

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.