Workbook
Download
This is a zipped Excel Workbook to go with this lesson.
Validating The Users Inputs
In the last lesson we looked at how a MultiPage control could be used. There are no hard and fast rules for which controls should be used for what purpose, only guides. If the truth be known the only limit to this is sometimes your own imagination and ability as a developer. As a developer myself I have seen many Excel projects that have incorporated UserForms. Some of these look great, but certainly don't live up to their expectations. In other words they are all gloss and no guts. As you may now be starting to realize, formatting a UserForm to look great is not that hard! This is a bit like those fantastic looking charts that really tell us nothing. From my personal experience, if you develop a UserForm for others to use, keep it gray and only add the 'bells and whistles' (if you must) once it has been proven to work. To show you some examples of what I mean by this, take a good look around Excel itself. Each time you select a menu option that shows a UserForm (Tool>Options) you don't see a multitude of colors thrust at you simply because Excel is used by millions of people all around the world. To try and second guess the favourite color of these users would be silly. So while you may make your UserForm look great in your eyes, don't assume another user will think the same. What I am saying in whole is spend that extra effort on the functionality of the UserForm and not the presentation. Gray is good!
One of the biggest problems that is (or should be) faced by a developer (other than understanding just what they want) is developing a project that will only accept valid data. By far the quickest and easiest way to achieve this is to use a ListBox and/or a ComboBox that presents the user with only valid data. The approach is one that I will try to employ whenever possible. There is also an added bonus to doing this and that is the user does not need to type, which means no typos! To ensure the user has made a valid selection from a ComboBox (and not typed in something not from the list) use the ListIndex Property of the ComboBox.
If ComboBox1.ListIndex < 0 Then
MsgBox "Your entry must be part of the list", vbCritical
Exit Sub
End If
Why 0 ? because the ListIndex value of the first row in a list is 0, the value of the second row is 1, and so on....
This very simple method is virtually foolproof and is a very easy way to ensure the entry in the ComboBox is part of the list. There are 2 important points to note here:
A message box tells the user what the problem is. Don't use a message like "Invalid Data!" only as the user will be left wondering why.
We have used "Exit Sub" this will cause our code to go no further and stop the rest of the Procedure from running. Don't use the keyword End for this as this will not only stop the Procedure from running, but unload the entire UserForm. Quite annoying if the user has just inputted into 20 or so TextBoxes, ComboBoxes etc as they will loose the lot!
Sometimes you may want to add this new entry to the ComboBox list if it's not already part of the list. This will depend on how the list is being read into the ComboBox.
AddItem
RowSource
If it's via the AddItem method then this is very easy just use:
Dim iReply As Integer
Dim vComboEntry As Variant
If ComboBox1.ListIndex < 0 Then
iReply = MsgBox("Your entry is not part of the list." _
& "Do you wish to add it", vbYesNoCancel)
Select Case iReply
Case vbNo
'<carry on code>
Case vbYes
vComboEntry = ComboBox1.Value
ComboBox1.AddItem (vComboEntry)
'<carry on code>
Case vbCancel
ComboBox1.SetFocus
Exit Sub
End Select
End If
If it's via the RowSource
method then use:
Dim iReply As Integer
Dim vComboEntry As Variant
If ComboBox2.ListIndex < 0 Then
iReply = MsgBox("Your entry is not part of the list." _
& "Do you wish to add it", vbYesNoCancel)
Select Case iReply
Case vbNo
'<carry on code>
Case vbYes
vComboEntry = ComboBox2.Value
Range("ComboBox_List").End _
(xlDown).Offset(1, 0) = vComboEntry
Range("ComboBox_List").Resize _
(Range("ComboBox_List").Rows.Count + 1).Name =
"ComboBox_List"
ComboBox2.RowSource = "ComboBox_List"
'<carry on code>
Case vbCancel
ComboBox2.SetFocus
Exit Sub
End Select
End If
The most important part of both of these codes is that we allowed the user to make a choice as to whether they want to add their entry to the list or not. If they say No, then we should carry on without adding it. If they say Cancel we place them back in the ComboBox (ComboBox1.SetFocus) and let them make any changes. If they say Yes then we add it. The AddItem method is pretty straightforward, but you should realize that even if they say Yes, the new entry will no longer be part of the list soon as the UserForm unloads! the RowSource method is slightly more complicated, but the one I would opt for in most cases as the new entry will be permanently part of the list.
What we have done in the case of the RowSource code is used the Variant variable "vComboEntry
" to store the value of the ComboBox. We used a Variant so that it can be a number or text. We then used the End(xlDown) method with the Offset to place our new entry at the bottom outside of the named range "ComboBox_List". As soon as the new entry was added to the outside of the named range we used the Resize Method to re-define our named range. The Resize method takes 2 arguments, both of which are optional:Row Size
Column Size
We used "Range("ComboBox_List").Rows.Count + 1" to resize the rows in ComboBox_List by one row, this accounts for the new entry. We then immediately reset the RowSource of the ComboBox back to "ComboBox_List". This is needed for our ComboBox to reflect the new range.
You could if you prefer use a Dynamic range for your ComboBox, but you will still need to reset the RowSource. Dynamic Ranges http://www.ozgrid.com/Excel/DynamicRanges.htm
Some Useful Methods and Functions
Unfortunately, it's not always possible to use a ComboBox or ListBox and we may need to allow the user to type in an entry. This means you will need to check whether the user has typed in a valid entry by seeing if it's a:
Number
Text
Date
Cell Address
Time
Email Address
Web Address
Exists Already
Doesn't Exist Already
Sheet Name
File Exists
and so the list goes on....
Fortunately most entries can be checked for validation, for an example see these below:
Check if numeric
If Not
IsNumeric(TextBox1.Value) Then
MsgBox "Numbers only", vbCritical
Exit Sub
End If
Check if Text
If
IsNumeric(TextBox1.Value) Then
MsgBox "Text only", vbCritical
Exit Sub
End If
Check if Date
If
IsDate(TextBox1.Value) Then
MsgBox "Text only", vbCritical
Exit Sub
End If
Check if Cell Address
Dim rCheck As Range
On Error Resume Next
Set rCheck = Range(TextBox1.Value)
On Error GoTo 0
If rCheck Is Nothing Then
MsgBox "Cell address only", vbCritical
Set rCheck = Nothing
Exit Sub
End If
Set rCheck = Nothing
The same principle can be used to check for a Sheet name. For example:
Set wsCheck = Sheets(TextBox1.Value)
Check if Time
Dim dTime As Date
If TextBox1.Text <> "12:00:00 AM" Then
On Error Resume Next
dTime = WorksheetFunction.Sum(TextBox1.Value, 1)
On Error GoTo 0
If dTime > DateValue("1/1/1900") Or dTime = "12:00:00 AM" Then
MsgBox "Times only", vbCritical
Exit Sub
End If
End If
The default value of a Date variable is 12:00:00 AM. This means that if you add text to it the result will be 12:00:00 AM. If we add a 1 to the a valid time and parse this to a Date variable it will return a result that is less than the date 1/1/1900. The whole process of date and times can get very confusing very quickly, so if you are not familiar with how Excel sees them, then I strongly suggest you read "How Excel stores dates and times" in the Excel help.
Check if Email
If InStr(1, TextBox1.Text, "@", vbTextCompare) = 0 Then
MsgBox "Not a valid email", vbCritical
Exit Sub
End If
The same principle can be used to check for a Web address (www or http or .com etc) see Excel VBE help on InStr Function!
Check if Exists Already
If WorksheetFunction.CountIf(Sheet1.Range("A1:C200"), TextBox1.Text)
<> 0 Then
MsgBox "Entry is already part of the Table", vbCritical
End If
The same principle can be used to check whether an entry doesn't exist already simply by changing the comparison operator to =
Check if File Exists
With Application.FileSearch
.NewSearch
.LookIn = "C:/MyDocuments"
.SearchSubFolders = True
.Filename = TextBox1.Text
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute = 0 Then
MsgBox "File does not exist", vbCritical
Exit Sub
End If
End With
I would also strongly suggest reading all the Excel VBE help on the FileSearch Property.
All the above methods are possibly some of the more common validation checks needed, I'm sure you realize that the list could go on and on. With regards to the last method of checking if a file exists or not there is another method which should be used whenever possible. This is the GetOpenFileName Method, which will display the standard Open dialog box and allow the user to browse to the required file. The result can then be parsed back to a String variable and used to open the file.
Dim stFileName As String
stFileName = Application.GetOpenFilename
If stFileName = "False" Then
Exit Sub 'They cancelled
Else
TextBox1.Text = stFileName
End If
The string returned will be the full path of the file chosen so you can easily use this with the Workbook.Open Method. I would in fact say that you should opt for this method over all others as allowing a user to type in a file name and path is asking for trouble.
The bottom line with checking for valid entries is be as pessimistic as you can. From my past experience if a user can get it wrong, they will! You will also find that for many validation checks it simply will not be possible to account for all possible errors, all we can do is aid the user in his/her entries.
The Workbook included with this lesson shows most of the above examples in practice. There is some code in the UserForms Private sub that we haven't discussed here, but will be in the next lesson. This will be on when to apply the validation that we have looked at in this lesson.
©2002 ozgrid - microsoftexceltraining - David & Raina Hawley. All right reserved