EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

 Excel Training VBA 2 Lesson 11

 

Some Useful Methods and Functions For UserForms


<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX

Workbook Download
    This is a zipped Excel Workbook to go with this lesson.

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.

Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX