 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
|
|
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.