Validating Userform Data

Hope you got everything you wanted for the holidays!

In Restricting character input in a textbox on a userform I complained about inline textbox validation. So I feel compelled to produce some sample procedures that illustrate what I see as a better way to validate userform data.

To recap, I recommend you wait until the end user has to submit the form before performing any kind of validation. Server-side validation, if you will, instead of client-side. I know I would get aggravated when filling in a VBA userform if I was prevented from entering certain characters, or they were dropped with no explanation.

So without further ado, here are some sample procedures for validating fields on your userform.

Checking if a field accepts user input

The very first thing we need to do is when validating a control, to make sure it can take user input. This is because we are designing generic procedures that need to have text values passed to them, and if we pass just any old control, those procedures will fail. The following controls can take user input:

• ComboBox
• TextBox

I'm not even going to touch RefEdit. Did I forget any? This procedure tells us if we have a control that takes text input:

Function TakesTextInput(ctl As MSForms.Control) As Boolean
  Dim allowedTypes() As String
  Const ALLOWED_TYPES As String = "ComboBox,TextBox"
  
  allowedTypes = StringToArray(ALLOWED_TYPES)
  ' only combo and text boxes take typed input
  TakesTextInput = (UBound(Filter(allowedTypes, TypeName(ctl))) > -1)
End Function

The StringToArray function may be found at Convert a String to an Array.

So from now on, we'll only be acting on TextBox and ComboBox controls. The best part is, these procedures may be placed in the code module behind a form or inside a standard module in the same project.

Sample Usage:

Pass a Control Object to the procedure and returns True if it allows typed input or False if it does not. Note that we do not check if a given control is enabled.

Public Sub CommandButton1_Click()
  Debug.Print TakesTextInput(Me.CommandButton1)
End Sub

We can also loop through all controls on a userform:

Dim ctl As MSForms.Control
Dim ctls As MSForms.Controls

Set ctls = Me.Controls

For Each ctl In ctls
  Debug.Print TypeName(ctl) & " takes text input: " & TakesTextInput(ctl)
Next ctl

This code would be placed inside the code module behind a userform. However we can check this in a standard module by passing a copy of the form outside:

Sub ShowTextInputFields(frm As MSForms.UserForm)

Dim ctl As MSForms.Control
Dim ctls As MSForms.Controls

Set ctls = frm.Controls

For Each ctl In ctls
  Debug.Print TypeName(ctl) & " takes text input: " & TakesTextInput(ctl)
Next ctl

End Sub

Inside the userform's code module you would write: ShowTextInputFields Me

Checking if a control contains only numbers

This one is simple: IsNumeric

According to Excel's Visual Basic Help:

Returns a Boolean value indicating whether an expression can be evaluated as a number. IsNumeric returns True if the entire expression is recognized as a number; otherwise, it returns False.

Function ContainsOnlyNumbers(ctl As MSForms.Control) As Boolean
  If TakesTextInput(ctl) Then
    ContainsOnlyNumbers = IsNumeric(ctl.value)
  End If
End Function

Once again, this function may be placed behind a userform's code module, or in a standard module.

Sample Usage:

If TakesTextInput(Me.CommandButton1) Then
  Debug.Print ContainsOnlyNumbers(Me.CommandButton1)
End If

Checking if a control contains a date

Another simple one: IsDate

From Excel's Visual Basic Help:

Returns a Boolean value indicating whether an expression can be converted to a date. IsDate returns True if the expression is a date or is recognizable as a valid date; otherwise, it returns False.

Function ContainsDate(ctl As MSForms.Control) As Boolean
  If TakesTextInput(ctl) Then
    ContainsDate = IsDate(ctl.value)
  End If
End Function

If the control takes user input, check to see if it's value can be interpreted as a date.

Checking if a control contains only text

This one is a little tricky. Unfortunately, we'll need to loop. Well, we don't really HAVE to, it's just the simplest way.

Function ContainsOnlyText(ctl As MSForms.Control) As Boolean
  Dim numChars As Long
  Dim i As Long
  Dim letters() As String
  
  Const LETTER_LIST As String = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z"
  letters = StringToArray(LETTER_LIST)

  If TakesTextInput(ctl) Then
    ' assume true
    ContainsOnlyText = True

    numChars = Len(ctl)
    For i = 1 To numChars
      If UBound(Filter(letters, Mid$(UCase$(ctl.value), i, 1))) = -1 Then ' not a letter, exit
        ContainsOnlyText = False
        Exit Function
      End If
    Next i
  End If
End Function

We could also use regex with a pattern of "[A-Za-z]", that would certainly make the code shorter. Anyone want to contribute the code?

Checking that all controls are filled

This procedure is a variation of what I use in my addins to check if the Submit button can be enabled. It checks all the user input fields (again, TextBox and ComboBox controls) and returns False if any are blank. Like the others it can be placed inside the code module behind a userform or in a standard module.

Function FormReadyToSubmit(frm As MSForms.UserForm) As Boolean

  Dim ctl As MSForms.Control
  Dim ctls As MSForms.Controls

  Set ctls = frm.Controls

  ' assume true
  FormReadyToSubmit = True

  ' check each user input control, if blank then return false
  For Each ctl In ctls
    If TakesTextInput(ctl) Then
      If Len(ctl.value) = 0 Then
        FormReadyToSubmit = False
        Exit Function
      End If
    End If
  Next ctl
End Function

Unlike the validation procedures above, I would put the FormReadyToSubmit method call into the Change Event of every control on a userform, because any given control might be the last one being updated before the form data is ready to be processed.