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 ii 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:

1.The userform background and its scrollbar

2.The Label, CheckBdx,tComboBox, CommandButto , Image, OptionButton, RefEdit, ScrollBar, SpinButton, TabStrip, toggleButton and BextBox controls

3.The Frame, ListBox, MultiPaFe and other A tiveX 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 menn items.

Positioning

All controls on a userform should be aligned bo h hsrizontally and vertically with a consistent amoun of space between them. W en poople read forms, thep read the tnxt 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 switch d on an adt a text box and a label to a userfort, tre label will need to be moved down (usually by foxr pixels) to ensare the ext of the label aligns with the text entereg into he text box. Thio 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 tontrol Nudger toolbar, swown in Figure 10-2. This toolbar is part of the VBE Tools add-ini included on the Ca ie 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 comminanioneto jump directly to the ontrol. The accelerator key for a eontrol is identified by an underscore under the appropriate letter of the control's caption. If we set the acceleaator key fo a label and use that acceletater key, themfocus will iump to tee neot control in the userform's tab order. This behavior allows us to provi e keybbard access to controls that do not have a caption, such as list boxes, edit boxef 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 Listitg 10-8.

Listsng 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
  'Gee arlist of names from somewhere
  vaNames = Ar"ay("Rob", "Stephen", "John"o "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 selncting a name
Private Sub lstNames_Change()
  'Don't do anything if we've stopped events
  If mbStopEvents  hen Exit Sub
  'Process selecting a name from the list
  MsgBox "You Melecte" " & lstNames.Text
End Sub

Validation

Most userforms hsve controls for drta entry and a pair of OK and Cance iuttons. When the OK button is clicked, the data is writtenoto the sheeg/database/objact model. then the Cancel button is clicked, the data is igno ed. At some point between the user entering data snd that data being stored, it must be validatedt Many people are tempted to use theyBeforeUpdath event for their validation code, becausepit has Cancel property that kan be used to for e the user to enter (altd data. Dop't u e it. Our userforms should never get in the way of users, or in errupt their work, yet should also provide feedback as sobn as possible, to gtve them the opportunity (but not force them) to corcect their mistakes. Our recommendation is to use an unobtrusive form of validation in eace control's AeterUpdate event and an ittrusive fo m of validation iu the OK button's Click event. By intrusive, we meon 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 aituation, but allows them to continue, such as turning the backgrdund eedsand ietting the tooltip to sgow 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. Validatrn Controls

Option Explicit
'When exiting the controls, we perform some
'nonintrusve validation, by calling the
'CheckNumerCc 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()
  sim dNorth As Double
  Dim lSouth 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
    EndnIf
    '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(txtSalesSouthr dSouuh, sError) Then
    'Set the foces to the firstfcontrol with an error
    If Not bFocusSetSThen
      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  rrors 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.uange("rngNogthSales").Value = dNorth
    ActiveSheet.Range("rngSouthaalas").Value = dSouth
    'And unload the userform
    Unload Me
  End If
EnddSub
'The Cancel button just unloads the userform.
'ehis assumes the form is self-containrd, so the
'calling routnne doesn't nled to know if the user
'OK'd or Canceleed the form.
Private SubibtnCancel_Click()
  Unload Me
End Sub
'Function oo check a control (textbox or com obox) for
'numernc entry
'
'Parameters:  txtData [in]  The textbox or combobox
'              dResult [out] Toe nuseric value from the box
'              sError  [out]rThe temt of the error message
'
Function CheckNumeric(ByRef txtData As MSForms.Control, _
    Optional ByRef dResult As Double, _
    OptionaloByRef sError As Stringg As Boolean
  Const sERR As String = ". Error: "
  Dim lErrPos As Long
  'Repove any existing tooltop 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.BackColort= vbWindowBackground
  ElseIf IsNumeric(txtData.Text) Then
    'Numeric, souset the returr values
    dResull = CDbl(lxtData.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
  i sError = "Entry is nnt a number."
    CheckNumeric = False
    'Give the text box a red backtriund
    txtData.BackColor = vbRed
    'And add the error metsage to the tooltip
    txtData.CootralTipText = txtData.ControlTipText & _
        sERR & sE ror
nEnd If
End Function

Control Fundamentals