AITKENonOffice

LEVEL: New Programmer VBA | UserForms

Introduction to UserForms: Part II

Handling Events

By Peter G. Aitken

In Part I, I started showing you how to create custom visual interfaces for your Office project with UserForms. A UserForm is a dialog box that can contain any of the standard Windows elements, named controls. They include text boxes for display and entry of data, buttons to carry out commands, and option buttons for making choices. Previously, you saw how to create a UserForm, place controls on it, and set properties to define the dialog box's appearance. You also saw how to display a UserForm from your VBA code and how to access the information the user enters on the form. Now, I will show you how your form can use VBA code and events to provide a fully customized and sophisticated part of your program's visual interface.

UserForm Code and Event Procedures

A UserForm and the controls it contains can detect events. For the most part these are things the user does, such as clicking a button or typing in some text. By writing code to respond to these events, you can make a UserForm truly responsive to the user.

The UserForm and each type of control can detect defined sets of events. Many of these events are common to most or all controls. The Click event is a good example. Other events are detected by only one or a few controls. When you display online help for a control, you can click the Events link to get a list of events the control supports. Likewise, when viewing the help information for an event, click the Applies To link to see which controls can detect the event.

Your program responds to events by means of event procedures (sometimes described as event handlers). An event procedure is a VBA procedure that is called automatically when the specified event occurs. Event procedures are identified by their names, as shown here:

Private Sub ObjectName_EventName()
  
End Sub

ObjectName is the name of the control, and EventName is the name of the event. For example, the Click event procedure for a TextBox control named txtName would be txtName_Click. You do not have to create these event procedures yourself. When you are working in a UserForm's code window, there are two drop-down lists at the top. The one on the left lists all the objects that are part of the UserForm: the UserForm itself, all controls you have placed on the form, and a (General) category that refers to module-level code. The list on the right contains all procedures for the object selected in the left list. To create an event procedure for a control or for the UserForm, select the object in the left list and the event in the right list. The VBE inserts the event procedure for you, including any necessary arguments. Of course, the procedure is empty, and you must add the code to carry out the actions needed.

You will use some events frequently.

The Change event. The Change event fires when a control's setting changes. To be precise, it fires when the control's Value property changes. The user can initiate that change, such as when he or she clicks a check box. Or the change can be a result of code, for instance if a TextBox control's Text property is changed.

The Click event. The Click event fires when the user clicks the object with the mouse's left button. It also fires for some controls when the value is changed via the keyboard, such as when the value of a CheckBox control changes because the user has moved the focus to it and has pressed [SpaceBar].

The DblClick event. The DblClick event occurs when the user double-clicks an object. The DblClick procedure is passed a True/False argument named Cancel. If code in the event procedure sets Cancel to True, the control ignores the second click. You would use this with controls, such as an OptionButton, for which clicking would change its value, and double-clicking would change its value twice, to return it to the original setting. By setting Cancel to True, you could have a double-click change the value of the control (because only the first click is registered) while still firing the DblClick event. Here's an example:

Private Sub OptionButton1_DblClick(ByVal Cancel _
  As MSForms.ReturnBoolean) 
   ' Other code to handle the DblClick event, _
   ' if needed, goes here. 
  Cancel = True
End Sub

The Enter and Exit events. The Enter event occurs when a control receives the focus, but only if the focus moves to the control from another control on the same form. Likewise, the Exit event occurs when the focus leaves a control and moves to another control on the form. These events occur before the focus moves. The Exit event is passed an argument named Cancel. If you set Cancel to True, the focus cannot move.

The KeyDown and KeyUp events. The KeyDown event occurs when the user presses a key and a control has the focus. The KeyUp event works exactly the same way but fires when the user releases the key. The syntax for the KeyDown event procedure is:

Private Sub
ControlName_KeyDown(ByVal KeyCode As _

 MSForms.ReturnInteger, ByVal Shift As Integer)

KeyCode is an integer value that provides the code of the key the user pressed. Because many keys do not correspond to characters, such as the function keys, the key code is not the same as an ASCII value. VBA provides handy constants for all the keycodes, such as vbKeyA for [A] and vbKeyNumpad5 for [5] on the numeric keypad. You can find a list of all these constants by searching for "keycodes" in the VBA online help.

The keycode identifies the key that the user has pressed, but not its shift state. For example, the keycode is the same for "A" and for "a." You determine whether the user also pressed the [Shift], [Ctrl], or [Alt] keys, based on the Shift argument. VBA provides defined constants you can use (see FIGURE 1).

FIGURE 1: VBA provides defined constants.

If the user pressed two or three of these keys, the value of the Shift argument is the sum of the corresponding values. For example, if both the [Ctrl] and [Shift] keys are down when the user presses a key, Shift will have the value 3. Here is an example of a KeyDown event procedure that executes some code only when the user presses [Shift][F9] while the control has the focus:

Private Sub TextBox1_KeyDown(ByVal KeyCode As _
 MSForms.ReturnInteger, ByVal Shift As Integer)
   If KeyCode = vbKeyF9 And Shift = fmShiftMask Then
     ' Code goes here. 
   End If
End Sub

Note that pressing the [Shift], [Ctrl], or [Alt] key alone does not generate an event. You can cancel a keystroke in the KeyDown event procedure by setting KeyCode to 0. Doing this, however, does not prevent the KeyUp event from firing.

The KeyPress event. The KeyPress event occurs when the user presses and releases a character key while a control has the focus. The syntax is:

Private Sub object_KeyPress(ByVal KeyAscii As _
    MSForms.ReturnInteger).

The procedure's one argument is the ASCII code of the key the user pressed. Note that this differs from the KeyDown and KeyUp event procedures, which are passed a keycode and not an ASCII code. One important use for the KeyPress event procedure is to restrict input to a TextBox control to certain characters. Code in the event procedure can examine the KeyAscii argument to determine which key the user pressed. If that key is not allowed, setting KeyAscii to 0 cancels the keystroke. FIGURE 2 presents an example that will permit the user to enter only the digits 0-9 in the TextBox. This code makes use of the fact that the characters 0 through 9 have ASCII values 48-57. You'll find a complete list of ASCII codes in the VBA online help. This procedure easily could be modified to permit the entry of a minus sign and a decimal point should your program need them.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As _
 MSForms.ReturnInteger) 
   If KeyAscii < 48 Or KeyAscii > 57 Then
    KeyAscii = 0
   End If
End Sub

FIGURE 2: The user can enter only the digits 0-9 in the TextBox control.

The KeyPress event procedure fires only when a character key is pressed. Character keys are:

• Any printable keyboard character
• [Ctrl], combined with a character from the standard alphabet
• Y
• [Esc]

The KeyPress event does not occur for these keys, however:

• [Enter]
• F
• An arrow key
• A keystroke that causes the focus to move

How does the KeyPress event relate to the KeyDown and KeyUp events? For one thing, you must use KeyDown to detect non-character keys. You can use either KeyPress or KeyDown to cancel character keys. When the user presses and releases a key, the three events fire in this order: KeyDown, KeyPress, and KeyUp.

The MouseDown, MouseUp, and MouseMove events. The MouseDown and MouseUp events fire when the user presses or releases a mouse and the pointer is over a control. The MouseMove event fires when the mouse pointer moves over a control. These three event procedures have essentially identical forms, as shown in FIGURE 3.

Private Sub ControlName_MouseDown(ByVal Button As _
 fmButton, ByVal Shift As  fmShiftState, _
  ByVal X As Single, ByVal Y As Single)
  
Private Sub ControlName_MouseUp(ByVal Button As _
 fmButton, ByVal Shift As fmShiftState, _
  ByVal X As Single, ByVal Y As Single)
  
Private Sub ControlName_MouseMove(ByVal Button As _
 fmButton, ByVal Shift As fmShiftState, _
  ByVal X As Single, ByVal Y As Single)

  ByVal
X As Single, ByVal Y As Single)

FIGURE 3: The MouseDown, MouseUp, and MouseMove procedures.

The Button argument identifies which mouse button the user pressed. If the user has not pressed a button, which would be the case only for MouseMove, the value is 0. FIGURE 4 shows the possible values for this argument and the VBA constants you can use.

FIGURE 4: VBA constants for the Button argument.

The Shift argument identifies whether the user pressed [Shift], [Ctrl], or [Alt] when the event fired. Again, there are VBA constants you can use in your code, as shown in the table in FIGURE 5.

FIGURE 5: Constants for the Shift argument.

The X and Y arguments provide the location of the mouse pointer relative to the top-left corner of the UserForm. These values are specified in units of points (1/72 of an inch).

With so many mouse-related events, it can be a little confusing trying to figure out when each event happens. When the user clicks an object, the mouse-related events occur in the following order: MouseDown, MouseUp, and Click.

When the user double-clicks, here's what happens: MouseDown, MouseUp, Click, DblClick, and MouseUp.

Like all Windows dialog boxes, UserForms have a behavior described as capturing the mouse. This means that if the mouse button is depressed while the pointer is over a control, that object receives all mouse events up to the final MouseUp, even if the pointer has been moved off the object before the mouse button is released. Even so, the X and Y values passed to the MouseUp event procedure will reflect the actual pointer position.

Displaying, Using, and Hiding UserForms

After designing a UserForm, how do you use it in your application? How do you display it, work with its controls, and then hide it? Let's look at the basic steps involved.

Remember that the UserForm you design is a template, and, to use it, you must create an instance of it.

If the form you designed was named DataInputForm, you would use the following syntax:

Dim MyForm As New DataInputForm

Then, you would have an instance of the form named MyForm. Next, you would perform any required initialization, such as setting control values:

MyForm.txtDate.Value = Date

Then, you would display the form using its Show method:

MyForm.Show

All the code up to this point would not be located in the form, but in another part of your program. At this point, the form would be displayed on the screen for the user. When the user had finished with the form, it would be hidden by calling its Hide method. This is usually done in the Click event procedure for a command button:

Private Sub cmdOK_Click()
  Hide
End Sub

This Click event procedure would be part of the form's own code. Note that because the code would be running in the form, you could call Hide without explicitly referring to the form.

After the form was closed, your program could retrieve data from the form's controls:

Cost = MyForm.txtCost.Value

Finally, if the program wasn't going to need the form again, you could destroy it:

Set MyForm = Nothing

Putting UserForms to Work

I will leave you with a UserForm demonstration. Although fairly simple, this form uses most of the techniques you need to take full advantage of UserForms in your VBA programs. The form is designed to permit the user to order an article of clothing, and even allows the user to select style and color. Once the user has made such choices, he or she can either place or cancel the order, and the relevant information is summarized in a MessageBox.

Start with the following steps:

1. Open the VBE and use the Insert | UserForm command to add a new UserForm to the project.
2. Change the form's Name property to OrderForm and its Caption property to Enter Order Details.
3. Add a Label control near the top-left of the form. Change its Caption property to Your name:.
4. Add a TextBox just to the right of the label. Change its Name property to txtName.
5. Draw a Frame control on the form. Change its Caption property to Color.
6. Draw four OptionButton controls directly on the Frame you just added. Change their Name and Caption properties as FIGURE 6 shows.
7. Add a CheckBox control. Change its Name to chkCatalog and its Caption to Send a Catalog.
8. Add a CommandButton to the form. Set its Name property to cmdOrder and its Caption to Place Order. In addition, set its Default property to True, which means the user can select the button by pressing [Enter] as well as by clicking it.
9. Add a second CommandButton with Name set to cmdCancel and Caption set to Cancel. Then, set the Cancel property to True, so the user can select the button by pressing [Esc].

FIGURE 6: Name and Caption properties for the OptionButton controls in the demo.

Don't forget to save your project. At this point, the UserForm design is complete, and your form will look something like FIGURE 7.

FIGURE 7: Designing the UserForm.

The next task is to write the form's code. What will this code need to do? If the user clicks the Place Order button, it should:

1. Verify the user entered a name
2. Verify the user selected a color
3. Close the form

On the other hand, if the user selects Cancel, all the form needs to do is close. However, how will the calling program know which button was clicked? You can create a variable named Cancelled in the form. If the user clicks Place Order, the variable will be set to False. But, if the user clicks Cancel, it will be set to True. The calling program can check this variable to determine what the user did.

To edit the code, select the UserForm and click the View Code button at the top of the Project window. Then:

1. Select (General) from the list at the top-left of the code window and type in the following line of code:

   Public Cancelled As Boolean

2. Select CmdCancel from the left list and Click from the right list to create the Click event procedure for the Cancel button. Enter the following code:

   Cancelled = True
   Hide

3. Select CmdOrder from the left list and Click from the right list to create the Click event procedure for the Place Order button. Then enter the code shown in FIGURE 8.

If txtName.Text = "" Then
  MsgBox ("Please enter your name.") 
   Exit Sub
End If
  
If optRed.Value = False And _
  optBlue.Value = False And _
  optWhite.Value = False And _
  optGreen.Value = False Then
  
  MsgBox ("You must select a color.") 
   Exit Sub
End If
Cancelled = False
Hide

FIGURE 8: The remaining code for the UserForm.

This completes the code for the UserForm. Next, you must create a VBA procedure that will display the UserForm and then read the data the user entered. This code will go in one of your VBA project modules. For example, if you are working in the VBE from Word, you could place it in the ThisDocument module by clicking ThisDocument in the Project window and then clicking the View Code button. You'll be adding a procedure, or macro, named TestUserForm. To do so, select Procedure from the Insert menu and then type the procedure name in the dialog box. Leave all other options at their default settings. The code for this procedure is shown in FIGURE 9.

Public Sub TestUserForm()
   Dim MyForm As New OrderForm
   Dim msg As String
  
  MyForm.Show
  
   If MyForm.Cancelled Then
    msg = "You cancelled the order" 
   Else
    msg = "Order details:" & vbCrLf
    msg = msg & "     Your name: " & _
     MyForm.txtName.Value & vbCrLf
If MyForm.optRed.Value Then
      msg = msg & "Red" 
     ElseIf MyForm.optBlue.Value Then
      msg = msg & "Blue" 
     ElseIf MyForm.optWhite.Value Then
      msg = msg & "White" 
     ElseIf MyForm.optGreen.Value Then
      msg = msg & "Green" 
     End If
  
    msg = msg & vbCrLf
     If MyForm.chkCatalog.Value Then
      msg = msg & "     Catalog requested: Yes" 
     Else
      msg = msg & "     Catalog requested: No" 
     End If
   End If
  MsgBox (msg) 
End Sub

FIGURE 9: The TestUserForm procedure tests the demonstration UserForm.

To run the procedure, place the editing cursor anywhere inside it and press [F5]. You'll see the UserForm displayed. When you close the form, a message box will pop up with a summary of your order.

Fashionable Forms

All Office developers should be aware of the power and flexibility of UserForms. By designing your own forms, you can customize an application's visual interface to meet the precise needs of the program and your client. Many VBA programs rely entirely on UserForms and never display the Office applications' native interface to the user. Such applications can appear to be completely custom, while, behind the scenes, you have all the power of the Office object model at your disposal. This can really impress a client and make you seem to be an even better developer than you are.

Peter G. Aitken has been writing about computers and programming for more than 10 years, with some 30 books and hundreds of magazine and trade-publication articles to his credit. Recent titles include Office XP Development with VBA (Prentice Hall, 2002) and Teach Yourself Internet Programming with Visual Basic in 21 Days (SAMS, 1998). Aitken is the proprietor of PGA Consulting (http://www.pgacon.com/pga_consulting.htm), which has provided custom application and Internet development to businesses, academia, and governments since 1994. Readers may reach him at mailto:peter@pgacon.com.