Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 37 ยท Forms, Events and Errors

UserForm Controls and Input Validation

Use ListBox, ComboBox, OptionButton, CheckBox, MultiPage and Frame controls in VBA UserForms, and validate input with Change, Exit and BeforeUpdate events.

After the basics of showing a form and reading a text box, the next step is using the full set of controls and making sure the data a user enters is valid. Good validation means the rest of your macro can assume clean input, which removes a whole class of run-time errors.

This lesson tours the main controls, shows how to fill list controls from a sheet, and builds a validation approach: check each field as the user works, and check everything again when OK is pressed.

VBA is read-only here. The runnable stand-ins are JavaScript versions of the validation functions and list logic, which you can test and adapt.

Common controls

Control Use Key properties
TextBox Free text or numbers Value, MaxLength, PasswordChar, MultiLine
ComboBox Drop-down choice or typed text List, ListIndex, Style (fmStyleDropDownList blocks typing)
ListBox Choose one or many rows RowSource, MultiSelect, Selected(i)
OptionButton One of several choices (group in a Frame) Value (True/False), GroupName
CheckBox On/off Value, TripleState
Frame / MultiPage Group controls, tabs Pages, Value (current page)
SpinButton, ScrollBar Increment values Min, Max, SmallChange
Image, Label Display only Picture, Caption

Set Style = fmStyleDropDownList on a ComboBox when only the listed options are valid. Option buttons that share a Frame (or GroupName) work as a radio group.

Filling lists

VB
Private Sub UserForm_Initialize()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Lists")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    cboDept.List = ws.Range("A2:A" & lastRow).Value      ' one assignment from a range
    lstItems.ColumnCount = 2
    lstItems.List = ws.Range("A2:B" & lastRow).Value

    cboDept.Style = fmStyleDropDownList
End Sub

.List = range.Value fills the control from a 2-D array in one step. AddItem works for one row at a time and is slower. RowSource links a list to a range directly, but it is read-only and breaks if the range moves.

Reading multi-select ListBox items

VB
Dim i As Long, picked As String
For i = 0 To lstItems.ListCount - 1
    If lstItems.Selected(i) Then picked = picked & lstItems.List(i, 0) & "; "
Next i

List indexes in controls start at 0, unlike worksheet rows.

Validation: two layers

Layer 1: as the user types. The KeyPress event can block bad characters, and Exit or BeforeUpdate checks a field when the user leaves it.

VB
Private Sub txtQty_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case 48 To 57, 8        ' digits and backspace
        Case Else: KeyAscii = 0 ' swallow everything else
    End Select
End Sub

Private Sub txtEmail_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(txtEmail.Value) > 0 And Not IsValidEmail(txtEmail.Value) Then
        MsgBox "That does not look like an e-mail address.", vbExclamation
        Cancel = True               ' keep focus in the box
    End If
End Sub

Layer 2: when OK is clicked. Check everything again, because the user can skip fields or paste text.

VB
Private Function IsFormValid() As Boolean
    Dim msg As String
    If Len(Trim$(txtName.Value)) = 0 Then msg = msg & "- Name is required" & vbCrLf
    If Not IsNumeric(txtQty.Value) Then
        msg = msg & "- Quantity must be a number" & vbCrLf
    ElseIf CLng(txtQty.Value) < 1 Then
        msg = msg & "- Quantity must be at least 1" & vbCrLf
    End If
    If cboDept.ListIndex = -1 Then msg = msg & "- Choose a department" & vbCrLf

    If Len(msg) > 0 Then MsgBox "Please fix:" & vbCrLf & msg, vbExclamation
    IsFormValid = (Len(msg) = 0)
End Function

Private Sub cmdOK_Click()
    If Not IsFormValid() Then Exit Sub
    ' ... save ...
    Unload Me
End Sub

Collecting all problems in one message is friendlier than showing one box per mistake. A note of caution: IsNumeric("1,5") and IsNumeric("$3") return True in some locales, so convert and test the result when exactness matters. Avoid Cancel = True loops that trap a user who wants to click Cancel; many designers only validate on OK for that reason.

Enabling and disabling controls

VB
Private Sub chkOther_Click()
    txtOther.Enabled = chkOther.Value
    If Not chkOther.Value Then txtOther.Value = ""
End Sub

Enable, disable or show/hide dependent fields so users cannot enter data that makes no sense. ControlTipText adds a tooltip, and TabIndex sets the Tab order.

Try it: validation logic

JS Collect all validation messages

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS KeyPress filter: digits and backspace only

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Trusting IsNumeric blindly, especially with decimals, currency symbols and locales.
  • Validating only in Exit events and not again on OK.
  • Using RowSource and then moving or deleting the source range.
  • Forgetting that control list indexes start at 0 while sheet rows start at 1.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which ComboBox Style prevents typing values that are not in the list?

    fmStyleDropDownList restricts the user to the listed items.
  2. 2. How do you fill a ListBox from a range in one step?

    Assigning the 2-D array from range.Value to List fills all rows and columns at once.
  3. 3. What does setting Cancel = True in an Exit event do?

    The user cannot leave the field until the value is valid.
  4. 4. Why validate again in the OK button?

    Users can skip fields, paste values or never visit a control, so a final check is essential.

Summary

  • Choose the control to match the data: ComboBox for single choices, ListBox for many, option buttons in frames, check boxes for flags.
  • Fill lists with .List = range.Value; indexes in controls start at 0.
  • Validate in two layers: KeyPress/Exit while typing and a full check on OK that reports all problems.
  • Enable or hide dependent controls to guide the user.

Next: Application and Workbook Events