Understanding UserForm Events
Each UserForm control (as well as the UserForm itself) is designed to respond to certain types of events, and these events can be triggered by a user or by Excel. For example, clicking a CommandButton generates a Click event for the CommandButton. You can write code that is executed when a particular event occurs.
Some actions generate multiple events. For example, clicking the upward arrow of a SpinButton control generates a SpinUp event and also a Change event. When a UserForm is displayed by using the Show method, Excel generates an Initialize event and an Activate event for the UserForm. (Actually, the Initialize event occurs when the UserForm is loaded into memory and before it is actually displayed.)
| CROSS-REFERENCE | Excel also supports events associated with a Sheet object, Chart objects, and the ThisWorkbook object. I discuss these types of events in Chapter 18. |
Learning about events
To find out which events are supported by a particular control, do the following:
-
Add a control to a UserForm.
-
Double-click the control to activate the code module for the UserForm. The VBE will insert an empty event handler procedure for the default event for the control.
-
Click the drop-down list in the upper-right corner of the module window, and you'll see a complete list of events for the control (see Figure 13-12).
Figure 13-12: The event list for a CheckBox control. -
Select an event from the list, and the VBE will create an empty event handler procedure for you.
| Note | To find out specific details about an event, consult the Help system. The Help system also lists the events available for each control. When you locate an event for an object, make sure the Help system table of contents is displayed. Then you can see a list of all other events for the object. |
| Caution | Event handler procedures incorporate the name of the object in the procedure's name. Therefore, if you change the name of a control, you'll also need to make the appropriate changes to the control's event handler procedure(s). The name changes are not performed automatically! To make things easy on yourself, it's a good idea to provide names for your controls before you begin creating event handler procedures. |
UserForm events
Several events are associated with showing and unloading a UserForm:
-
Initialize : Occurs before a UserForm is loaded or shown but does not occur if the UserForm was previously hidden.
-
Activate : Occurs when a UserForm is shown.
-
Deactivate : Occurs when a UserForm is deactivated but does not occur if the form is hidden.
-
QueryClose : Occurs before a UserForm is unloaded.
-
Terminate : Occurs after the UserForm is unloaded.
| Note | Often, it's critical that you choose the appropriate event for your event handler procedure and that you understand the order in which the events occur. Using the Show method invokes the Initialize and Activate events (in that order). Using the Load command invokes only the Initialize event. Using the Unload command triggers the |
QueryClose and Terminate events (in that order). Using the Hide method doesn't trigger either of these events.
| CD-ROM | The companion CD-ROM contains a workbook (named |
userform events.xlsm SpinButton events
To help clarify the concept of events, this section takes a close look at the events associated with a SpinButton control.
| CD-ROM | The companion CD-ROM contains a workbook that demonstrates the sequence of events that occur for a SpinButton and the UserForm that contains it. The workbook, named |
Table 13-1 lists all the events for the SpinButton control.
| Event | Description |
|---|---|
| AfterUpdate | Occurs after the control is changed through the user interface. |
| BeforeDragOver | Occurs when a drag-and-drop operation is in progress. |
| BeforeDropOrPaste | Occurs when the user is about to drop or paste data onto the control. |
| BeforeUpdate | Occurs before the control is changed. |
| Change | Occurs when the Value property changes. |
| Enter | Occurs before the control actually receives the focus from a control on the same UserForm. |
| Error | Occurs when the control detects an error and cannot return the error information to a calling program. |
| Exit | Occurs immediately before a control loses the focus to another control on the same form. |
| KeyDown | Occurs when the user presses a key and the object has the focus. |
| KeyPress | Occurs when the user presses any key that produces a typeable character. |
| KeyUp | Occurs when the user releases a key and the object has the focus. |
| SpinDown | Occurs when the user clicks the lower (or left) SpinButton arrow. |
| SpinUp | Occurs when the user clicks the upper (or right) SpinButton arrow. |
Open table as spreadsheet A user can operate a SpinButton control by clicking it with the mouse or (if the control has the focus) by using the up-arrow and down-arrow keys.
MOUSE-INITIATED EVENTS
When the user clicks the upper SpinButton arrow, the following events occur in this precise order:
-
Enter (triggered only if the SpinButton did not already have the focus)
-
Change
-
SpinUp
KEYBOARD-INITIATED EVENTS
The user can also press Tab to set the focus to the SpinButton and then use the arrow keys to increment or decrement the control. If so, the following events occur (in this order):
-
Enter
-
KeyDown
-
Change
-
SpinUp (or SpinDown )
-
KeyUp
WHAT ABOUT CHANGES VIA CODE?
The SpinButton control can also be changed by VBA code - which also triggers the appropriate event(s). For example, the following statement sets the SpinButton1 Value property to and also triggers the Change event for the SpinButton control - but only if the SpinButton value was not already :
SpinButton1.Value = 0
You might think that you could disable events by setting the EnableEvents property of the Application object to False . Unfortunately, this property applies only to events that involve true Excel objects: Workbooks , Worksheets , and Charts .
Pairing a SpinButton with a TextBox
A SpinButton has a Value property, but this control doesn't have a caption in which to display its value. In many cases, however, you will want the user to see the SpinButton value. And sometimes you'll want the user to be able to change the SpinButton value directly instead of clicking the SpinButton repeatedly.
The solution is to pair a SpinButton with a TextBox, which enables the user to specify a value either by typing it into the TextBox directly or by clicking the SpinButton to increment or decrement the value in the TextBox.
Figure 13-13 shows a simple example. The SpinButton's Min property is 1 , and its Max property is 100 . Therefore, clicking the SpinButton's arrows will change its value to an integer between 1 and 100.
Figure 13-13: This SpinButton is paired with a TextBox.
| CD-ROM | This workbook is available on the companion CD-ROM. The file is named |
The code required to link a SpinButton with a TextBox is relatively simple. It's basically a matter of writing event handler procedures to ensure that the SpinButton's Value property is always in sync with the TextBox's Text property.
The following procedure is executed whenever the SpinButton's Change event is triggered. That is, the procedure is executed when the user clicks the SpinButton or changes its value by pressing the up arrow or down arrow.
Private Sub SpinButton1_Change() TextBox1.Text = SpinButton1.Value End Sub
The procedure simply assigns the SpinButton's Value to the Text property of the TextBox control. Here, the controls have their default names ( SpinButton1 and TextBox1 ). If the user enters a value directly into the TextBox, its Change event is triggered, and the following procedure is executed:
Private Sub TextBox1_Change() NewVal = Val(TextBox1.Text) If NewVal >= SpinButton1.Min And _ NewVal <= SpinButton1.Max Then _ SpinButton1.Value = NewVal End Sub
This procedure starts by using VBA's Val function to convert the text in the TextBox to a value. (If the TextBox contains non-numeric text, the Val function returns .) The next statement determines whether the value is within the proper range for the SpinButton. If so, the SpinButton's Value property is set to the value entered in the TextBox.
The example is set up so that clicking the OK button (which is named OKButton ) transfers the SpinButton's value to the active cell . The event handler for this CommandButton's Click event is as follows :
Private Sub OKButton_Click() ' Enter the value into the active cell If CStr(SpinButton1.Value) = TextBox1.Text Then ActiveCell = SpinButton1.Value Unload Me Else MsgBox "Invalid entry.", vbCritical TextBox1.SetFocus TextBox1.SelStart = 0 TextBox1.SelLength = Len(TextBox1.Text) End If End Sub
 |
Every UserForm and control has a Tag property. This property doesn't represent anything specific, and, by default, is empty. You can use the Tag property to store information for your own use.
For example, you might have a series of TextBox controls in a UserForm. The user may be required to enter text into some but not all of them. You can use the Tag property to identify (for your own use) which fields are required. In this case, you can set the Tag property to a string such as Required . Then when you write code to validate the user's entries, you can refer to the Tag property.
The following example is a function that examines all TextBox controls on UserForm1 and returns the number of required TextBox controls that are empty:
Function EmptyCount() Dim ctl As Control EmptyCount= 0 For Each ctl In UserForm1.Controls If TypeName(ctl) = "TextBox" Then If ctl.Tag = "Required" Then If ctl.Text = "" Then EmptyCount = EmptyCount + 1 End If End If End If Next ctl End Function
As you work with UserForms, you will probably think of other uses for the Tag property.
| |
This procedure does one final check: It makes sure that the text entered in the TextBox matches the SpinButton's value. This is necessary in the case of an invalid entry. For example, if the user enters 3r into the TextBox, the SpinButton's value would not be changed, and the result placed in the active cell would not be what the user intended. Notice that the SpinButton's Value property is converted to a string by using the CStr function. This ensures that the comparison will not generate an error if a value is compared with text. If the SpinButton's value does not match the TextBox's contents, a message box is displayed. Notice that the focus is set to the TextBox object, and the contents are selected (by using the SelStart and SelLength properties). This makes it very easy for the user to correct the entry.
EAN: 2147483647
Pages: 319