Expert: Damon Ostrander - 3/11/2008

Question
Hi I'm new to VBA.  I have a program that uses a number of userforms which I would like to set up validation for.  I want to check that all the required fields have been completed and I want to check that numeric fields have numbers only entered.  I would also like to indicate to the user which fields are have been incorrectly completed.  Can you help suggest code to do this please?

Answer
Hi David,

There are a lot of ways of doing this, but the following illustrates a method I recommend.

Here is an example that should prove helpful. In this example, there is one combobox (ComboBox1) and three textboxes (TextBox1,...,TextBox3) on the userform.  When the user is done entering data in all four controls, the user then clicks CommandButton1 indicating he/she is done. The commandbutton's Click event code then checks to make sure all the fields have data. If any are empty those fields are turned pink and a messagebox informs the user that the pink fields must be filled in.

In this example I validated TextBox2 for numeric data, i.e., it is the only textbox that requires numeric input. As soon as the user enters a non-numeric value in the textbox an error message again pops up and the user cannot exit the textbox without entering a numeric value.

Here's the code:

Private Sub CommandButton1_Click()
  Dim FormIncomplete       As Boolean
  FormIncomplete = False
  If ComboBox1.Value = "" Then
     FormIncomplete = True
     ComboBox1.BackColor = RGB(255, 200, 200)   'pink
  Else
     ComboBox1.BackColor = RGB(255, 255, 255)   'white
  End If
  If TextBox1.Value = "" Then
     FormIncomplete = True
     TextBox1.BackColor = RGB(255, 200, 200)
  Else
     TextBox1.BackColor = RGB(255, 255, 255)
  End If
     If TextBox2.Value = "" Then
     FormIncomplete = True
     TextBox2.BackColor = RGB(255, 200, 200)
  Else
     TextBox2.BackColor = RGB(255, 255, 255)
  End If
     If TextBox3.Value = "" Then
     FormIncomplete = True
     TextBox3.BackColor = RGB(255, 200, 200)
  Else
     TextBox3.BackColor = RGB(255, 255, 255)
  End If
  If FormIncomplete Then
     MsgBox "Please complete highlighted fields", vbCritical, "Form not completed"
     Exit Sub
  End If
  Unload Me   'unload the userform
End Sub


Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If Not IsNumeric(TextBox2.Value) Then
     Cancel = True
     MsgBox "Value must be numeric", vbCritical, "Data entry error"
  End If
End Sub

Note that this code must independently check every control that must be completed because it is possible that multiple ones aren't completed and all must be identified (highlighted). If you have a very large number of such controls it would be possible to shorten the code by using a loop, but for a modest number (say, less than a dozen) I recommend including the code for each one as I have done.

Of course this code would have to be varied a bit if different validation requirements existed (for example, if the numeric value had to be a valid Zip code), or if you have other types of controls. But I think this at least gives you a template for handling these other types of situations.

Keep Excelling.

Damon