Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.
 
Prepared by David Hawley of OzGrid Business Applications.
Specialists in Microsoft Excel and VBA for Excel
Web: www.ozgrid.com
Email: davidh@ozgrid.com

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:

  1. 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.

  2. 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.

  1. AddItem

  2. 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:

  1. Row Size

  2. 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:

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.

 


Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation®.

Prepared by David Hawley of OzGrid Business Applications.
Specialists in Microsoft Excel and VBA for Excel
Web: www.ozgrid.com
Email:

©2002 ozgrid - microsoftexceltraining - David & Raina Hawley. All right reserved

: created: 30.Sep 2001 : : hans : san remo wa :
website design by: aardvark internet publishing, wa [ AVIP.com.au ]