AboutDamon Ostrander Expertise I have extensive experience with VBA programming in Excel 5 through Excel 2007. I am an consultant in a small defense technology services company, and have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.
Experience I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.
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.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
