Auto-Tabbing Fields and Formatting Text - Visual Basic 6 TextBox Control

Auto-Tabbing Fields

Users aren't usually delighted to spend all their time at the keyboard. Your job as a programmer is to make their jobs easier, and so you should strive to streamline their everyday work as much as possible. One way to apply this concept is to provide them with auto-tabbing fields, which are fields that automatically advance users to the next field in the Tab order as soon as they enter a valid value. Most often, auto-tabbing fields are those TextBox controls whose MaxLength property has been assigned a non-null value. Implementing such an auto-tabbing field in Visual Basic is straightforward:

Private Sub Text1_Change()
If Len(Text1.Text) = Text1.MaxLength Then SendKeys "{Tab}"
End Sub

The trick, as you see, is to have your program provide the Tab key on behalf of your user. In some cases, this simple approach doesn't work—for example, when you paste a long string into the field. You might want to write code that works around this and other shortcomings. Auto-tabbing is a nice feature but not vital to the application, so whether you write a workaround or not isn't a real problem in most cases.

Formatting Text

Many business applications let you enter data in one format and then display it in another. For example, numeric values can be formatted with thousand separators and a fixed number of decimal digits. Currency values might have a $ symbol (or whatever your national currency symbol is) automatically inserted. Phone numbers can be formatted with dashes to split into groups of digits. Credit-card numbers can be made more readable with embedded spaces. Dates can be shown in long-date format ("July 22, 2007"). And so on.

The LostFocus event is an ideal occasion to format the contents of a TextBox control as soon as the input focus leaves it. In most cases, you can perform all your formatting chores using the Format function. For example, you can add thousand separators to a numeric value in the txtNumber control using this code:

Private Sub txtNumber_LostFocus()
On Error Resume Next
txtNumber.Text = Format(CDbl(txtNumber.Text), _
"#,###,###,##0.######")
End Sub

When the field regains the focus, you'll want to get rid of those thousand separators. You can do it easily using the CDbl function:

Private Sub txtNumber_GotFocus()
' On Error is necessary to account for empty fields.
On Error Resume Next
txtNumber.Text = CDbl(txtNumber.Text)
End Sub

In some cases, however, formatting and unformatting a value isn't that simple. For example, you can format a Currency value to add parentheses around negative numbers, but there's no built-in Visual Basic function able to return a string formatted in that way to its original condition. Fear not, because nothing prevents you from creating your own formatting and unformatting routines. I have built two general-purpose routines for you to consider.

The FilterString routine filters out all unwanted characters in a string:

Function FilterString(Text As String, validChars As String) As String
Dim i As Long, result As String
For i = 1 To Len(Text)
If InStr(validChars, Mid$(Text, i, 1)) Then
result = result & Mid$(Text, i, 1)
End If
Next
FilterString = result
End Function

FilterNumber builds on FilterString to strip down all formatting characters in a number and can also trim trailing decimal zeros:

Function FilterNumber(Text As String, TrimZeros As Boolean) As String
Dim decSep As String, i As Long, result As String
' Retrieve the decimal separator symbol.
decSep = Format$(0.1, ".")
' Use FilterString for most of the work.
result = FilterString(Text, decSep & "-0123456789")
' Do the following only if there is a decimal part and the
' user requested that nonsignificant digits be trimmed.
If TrimZeros And InStr(Text, decSep) > 0 Then
For i = Len(result) To 1 Step -1
Select Case Mid$(result, i, 1)
Case decSep
result = Left$(result, i - 1)
Exit For
Case "0"
result = Left$(result, i - 1)
Case Else
Exit For
End Select
Next
End If
FilterNumber = result
End Function

The feature I like most in FilterNumber is that it's locale-independent. It works equally well on both sides of the Atlantic ocean (and on other continents, as well.) Instead of hard-coding the decimal separator character in the code, the routine determines it on the fly, using the Visual Basic for Applications (VBA) Format function. Start thinking internationally now, and you won't have a nervous breakdown when you have to localize your applications in German, French, and Japanese.

The Format function lets you retrieve many locale-dependent characters and separators.
Format$(0.1, ".") ' Decimal separator
Format$(1, ",") ' Thousand separator
Mid$(Format(#1/1/99#, "short date"), 2, 1) ' Date separator

You can also determine whether the system uses dates in "mm/dd/yy" (U.S.) format or "dd/mm/yy" (European) format, using this code:

If Left$(Format$("12/31/1999", "short date"), 2) = 12 Then
' mm/dd/yy format

Else
' dd/mm/yyyy format

End If

There's no direct way to determine the currency symbol, but you can derive it by analyzing the result of this function:

Format$(0, "currency") ' Returns "$0.00" in US

It isn't difficult to write a routine that internally uses the information I've just given you to extract the currency symbol as well as its default position (before or after the number) and the default number of decimal digits in currency values. Remember, in some countries the currency symbol is actually a string of two or more characters.

To illustrate these concepts in action, I've built a simple demonstration program that shows how you can format numbers, currency values, dates, phone numbers, and credit-card numbers when exiting a field, and how you can remove that formatting from the result when the input focus reenters the TextBox control. Follwoing figure shows the formatted results.

Formatting and unformatting the contents of TextBox controls makes for more professional-looking applications

Private Sub txtNumber_GotFocus()
' Filter out nondigit chars and trailing zeros.
On Error Resume Next
txtNumber.Text = FilterNumber(txtNumber.Text, True)
End Sub
Private Sub txtNumber_LostFocus()
' Format as a number, grouping thousand digits.
On Error Resume Next
txtNumber.Text = Format(CDbl(txtNumber.Text), _
"#,###,###,##0.######")
End Sub

Private Sub txtCurrency_GotFocus()
' Filter out nondigit chars and trailing zeros.
' Restore standard text color.
On Error Resume Next
txtCurrency.Text = FilterNumber(txtCurrency.Text, True)
txtCurrency.ForeColor = vbWindowText
End Sub
Private Sub txtCurrency_LostFocus()
On Error Resume Next
' Show negative values as red text.
If CDbl(txtCurrency.Text) < 0 Then txtCurrency.ForeColor = vbRed
' Format currency, but don't use parentheses for negative numbers.
' (FormatCurrency is a new VB6 string function.)
txtCurrency.Text = FormatCurrency(txtCurrency.Text, , , vbFalse)
End Sub

Private Sub txtDate_GotFocus()
' Prepare to edit in short-date format.
On Error Resume Next
txtDate.Text = Format$(CDate(txtDate.Text), "short date")
End Sub
Private Sub txtDate_LostFocus()
' Convert to long-date format upon exit.
On Error Resume Next
txtDate.Text = Format$(CDate(txtDate.Text), "d MMMM yyyy")
End Sub

Private Sub txtPhone_GotFocus()
' Trim embedded dashes.
txtPhone.Text = FilterString(txtPhone.Text, "0123456789")
End Sub
Private Sub txtPhone_LostFocus()
' Add dashes if necessary.
txtPhone.Text = FormatPhoneNumber(txtPhone.Text)
End Sub

Private Sub txtCreditCard_GotFocus()
' Trim embedded spaces.
txtCreditCard.Text = FilterNumber(txtCreditCard.Text, True)
End Sub
Private Sub txtCreditCard_LostFocus()
' Add spaces if necessary.
txtCreditCard.Text = FormatCreditCard(txtCreditCard.Text)
End Sub

Instead of inserting the code that formats phone numbers and credit-card numbers right in the LostFocus event procedures, I built two distinct routines, which can be more easily reused in other applications, as shown in the code below.

Function FormatPhoneNumber(Text As String) As String
Dim tmp As String
If Text <> "" Then
' First get rid of all embedded dashes, if any.
tmp = FilterString(Text, "0123456789")
' Then reinsert them in the correct position.
If Len(tmp) <= 7 Then
FormatPhoneNumber = Format$(tmp, "!@@@-@@@@")
Else
FormatPhoneNumber = Format$(tmp, "!@@@-@@@-@@@@")
End If
End If
End Function

Function FormatCreditCard(Text As String) As String
Dim tmp As String
If Text <> "" Then
' First get rid of all embedded spaces, if any.
tmp = FilterNumber(Text, False)
' Then reinsert them in the correct position.
FormatCreditCard = Format$(tmp, "!@@@@ @@@@ @@@@ @@@@")
End If
End Function

Unfortunately, there isn't any way to create locale-independent routines that can format any phone number anywhere in the world. But by grouping all your formatting routines in one module, you can considerably speed up your work if and when it's time to convert your code for another locale.