Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Control Fundamentals

There are a few fundamental details that we simply have to get right when working with controls on userforms.

Naming

As we discussed in Chapter 3 Excel and VBA Development Best Practices, all our controls should be given meaningful names and include a two- or three-character prefix to identify the control type. This enables us to easily identify the control in code and when setting the tab order. For example, we have no idea which button CommandButton1 is, but we can easily identify cmdOK and so on.

Layering

If we include the background, userforms have three drawing layers. When we add a control to a userform, it gets added to one of the top two layers, depending on the type of control. The three layers are identified as follows:

The userform background and its scrollbar

The Label, CheckBox, ComboBox, CommandButton, Image, OptionButton, RefEdit, ScrollBar, SpinButton, TabStrip, ToggleButton and TextBox controls

The Frame, ListBox, MultiPage and other ActiveX controls

Controls in layer 2 can overlap each other, but will always be drawn behind controls in layer 3, whereas all the controls in layer 3 can overlap each other. Fortunately, layer 3 includes the Frame control, so if we want to draw any of the other controls on top of a layer 3 control, we can put it inside a frame. Within a layer, we can arrange our controls' z-order using the Format > Order menu items.

Positioning

All controls on a userform should be aligned both horizontally and vertically with a consistent amount of space between them. When people read forms, they read the text of each control, so it is the text that we should align and not the edges of the controls. If we have Snap to Grid switched on and add a text box and a label to a userform, the label will need to be moved down (usually by four pixels) to ensure the text of the label aligns with the text entered into the text box. This can be done by editing the label's Top property, to add four pixels, but how big is a pixel? In Chapter 9 Understanding and Using Windows API Calls, we explained that the pixel size depends on the user's choice of dots per inch, but is usually 0.75 points for the Normal setting of 96 DPI and 0.6 points for the Large setting of 120 DPI. So to move a control by one pixel, we have to add 0.75 or 0.6. Moving and sizing controls pixel by pixel is made much easier by using the VBE Tools Control Nudger toolbar, shown in Figure 10-2. This toolbar is part of the VBE Tools add-in, included on the CD in the \Tools\folder.

Figure 10-2. The VBE Tools Control Nudger Toolbar

Tab Orders and Accelerator Keys

As designers of userforms, we tend to be "mousers" and often forget that many people prefer to use the keyboard to navigate around userforms. We must remember to facilitate keyboard usage by ensuring we give our controls the correct tab order and/or accelerator keys. The tab order should match the natural reading orderleft to right and top to bottomand should include labels as well as controls. If the userform includes some container controls, such as the Frame and MultiPage controls, we must remember to set the tab order for the controls they contain as well as for those directly on the userform. We do this by ensuring the container control is selected before clicking View > Tab Order.

Accelerator keys enable us to use the Alt+key combination to jump directly to the control. The accelerator key for a control is identified by an underscore under the appropriate letter of the control's caption. If we set the accelerator key for a label and use that accelerator key, the focus will jump to the next control in the userform's tab order. This behavior allows us to provide keyboard access to controls that do not have a caption, such as list boxes, edit boxes and so forth.

Data Binding

Many userform controls have properties that allow them to be bound to worksheet cells to specify their contents and/or return their value/text. Don't use them. They are there for beginners to create simple, quick-and-dirty forms, but they very quickly become more trouble than they're worth. Using VBA to set the controls' contents and handle the data entry gives us much more flexibility and enables us to validate the data before updating cells.

Event Handling

The MSForms controls used on userforms have a fairly rich event model. Deciding which event to use for a given purpose can be quite daunting. Our recommendation is to follow the principle of keeping it simple and use the basic Change or Click events for most situations.

In particular, don't try to intercept the KeyDown or KeyPress events in an attempt to force numeric entry into a control. If your code prevents letters, it also prevents the valid use of exponential notation, such as 1E3 for 1000. If you try to prevent multiple decimal separators, you have to make sure you're allowing the decimal separator set in the Regional Settings applet; and if the user put the decimal in the wrong place, you're forcing them to delete the wrong one before typing the new one. It is much better (and easier for us) to allow the user to type in whatever he chooses, then validate the entry using VBA's IsNumeric() function.

Control events are fired both by user action and when the control is changed in code. We can use Application.EnableEvents to turn events on and off for Excel's objects, but that has no effect on the MSForms object model. We can get the same level of control over when events are handled by using a module-level variable that is checked at the start of all our event procedures, as shown in Listing 10-8.

Listing 10-8. Handling Controls' Events

'Module-level variable to control events firing
Dim mbStopEvents As Boolean
'Handle clicking a 'Get Data' button
Private Sub btnGetNames_Click()
Dim vaNames As Variant
'Get a list of names from somewhere
vaNames = Array("Rob", "Stephen", "John", "Robert")
'Turn off events while populating the controls
mbStopEvents = True
'Populate controls.
'The Clear method triggers the Change event.
lstNames.Clear
lstNames.List = vaNames
'Turn events on again
mbStopEvents = False
'Select the first name, allowing the Change event to fire
If lstNames.ListCount > 0 Then
lstNames.ListIndex = 0
End If
End Sub
'Handle selecting a name
Private Sub lstNames_Change()
'Don't do anything if we've stopped events
If mbStopEvents Then Exit Sub
'Process selecting a name from the list
MsgBox "You selected " & lstNames.Text
End Sub

Validation

Most userforms have controls for data entry and a pair of OK and Cancel buttons. When the OK button is clicked, the data is written to the sheet/database/object model. When the Cancel button is clicked, the data is ignored. At some point between the user entering data and that data being stored, it must be validated. Many people are tempted to use the BeforeUpdate event for their validation code, because it has a Cancel property that can be used to force the user to enter valid data. Don't use it. Our userforms should never get in the way of users, or interrupt their work, yet should also provide feedback as soon as possible, to give them the opportunity (but not force them) to correct their mistakes. Our recommendation is to use an unobtrusive form of validation in each control's AfterUpdate event and an intrusive form of validation in the OK button's Click event. By intrusive, we mean something that stops the user from continuing, such as displaying a message box. By unobtrusive, we mean something that alerts the user to an error situation, but allows them to continue, such as turning the background red and setting the tooltip to show the error message. Listing 10-9 shows the code to validate a simple userform that contains two sales figures, and Figure 10-3 shows the userform with an error in the first edit box.

Listing 10-9. Validating Controls

Option Explicit
'When exiting the controls, we perform some
'nonintrusve validation, by calling the
'CheckNumeric function
Private Sub txtSalesNorth_AfterUpdate()
CheckNumeric txtSalesNorth
End Sub
Private Sub txtSalesSouth_AfterUpdate()
CheckNumeric txtSalesSouth
End Sub
'In the OK button, we use the same CheckNumeric
'function to show some intrusive validation
'messages.
Private Sub btnOK_Click()
Dim dNorth As Double
Dim dSouth As Double
Dim sError As String
Dim sAllErrors As String
Dim bFocusSet As Boolean
'Validate the North Sales text box,
'returning the value or some error text
If Not CheckNumeric(txtSalesNorth, dNorth, sError) Then
'Set the focus to the first control with an error
If Not bFocusSet Then
txtSalesNorth.SetFocus
bFocusSet = True
End If
'Build an error string, so we display all errors on the
'userform in one error message
sAllErrors = sAllErrors & "North Sales:" & sError & vbLf
End If
'Validate the South Sales text box,
'returning the value or some error text
If Not CheckNumeric(txtSalesSouth, dSouth, sError) Then
'Set the focus to the first control with an error
If Not bFocusSet Then
txtSalesSouth.SetFocus
bFocusSet = True
End If
'Build an error string, so we display all errors on the
'userform in one error message
sAllErrors = sAllErrors & "South Sales:" & sError & vbLf
End If
'Display any errors we got
If Len(sAllErrors) > 0 Then
MsgBox "Please correct the following error(s):" & _
vbLf & sAllErrors, vbOKOnly
Else
'No errors, so store the result
ActiveSheet.Range("rngNorthSales").Value = dNorth
ActiveSheet.Range("rngSouthSales").Value = dSouth
'And unload the userform
Unload Me
End If
End Sub
'The Cancel button just unloads the userform.
'This assumes the form is self-contained, so the
'calling routine doesn't need to know if the user
'OK'd or Cancelled the form.
Private Sub btnCancel_Click()
Unload Me
End Sub
'Function to check a control (textbox or combobox) for
'numeric entry
'
'Parameters:  txtData [in]  The textbox or combobox
'              dResult [out] The numeric value from the box
'              sError  [out] The text of the error message
'
Function CheckNumeric(ByRef txtData As MSForms.Control, _
Optional ByRef dResult As Double, _
Optional ByRef sError As String) As Boolean
Const sERR As String = ". Error: "
Dim lErrPos As Long
'Remove any existing tooltip error text
lErrPos = InStr(1, txtData.ControlTipText, sERR)
If lErrPos > 0 Then
txtData.ControlTipText = Left$(txtData.ControlTipText, _
lErrPos - 1)
End If
'Check for valid entry
If txtData.Text = " Then
'Allow empty
dResult = 0
sError = "
CheckNumeric = True
'And give the text box its usual background
txtData.BackColor = vbWindowBackground
ElseIf IsNumeric(txtData.Text) Then
'Numeric, so set the return values
dResult = CDbl(txtData.Text)
sError = "
CheckNumeric = True
'And give the text box its usual background
txtData.BackColor = vbWindowBackground
Else
'Not numeric, so set the return values
dResult = 0
sError = "Entry is not a number."
CheckNumeric = False
'Give the text box a red background
txtData.BackColor = vbRed
'And add the error message to the tooltip
txtData.ControlTipText = txtData.ControlTipText & _
sERR & sError
End If
End Function

Figure 10-3. An Unobtrusive Error Indicator