Use textboxes like date or phone number fields
November 5, 2009 • JP • No Comments • Rate This Article
• Links to this article
Textboxes on Excel userforms work a little different than their Access equivalents. For one thing, you can't specify an input format. A textbox is just, well, a textbox.
But with VBA, we can make our textboxes act like date or phone number fields. Just like Access!
Simply put, you need to write code for the Enter, Exit and AfterUpdate events to simulate what would happen if a given field holds a Date or Phone Number.
The first thing you want to do is place the following at the top of your form's module:
Const DEFAULT_DATE_FIELD As String = "__/__/____"
These are the constants that will be used to format the empty textboxes that we'll use for the date and phone fields. Here are the event handlers that make up the phone field:
On Error GoTo ErrorHandler
With Me.HomePhoneNumber
If .Value = DEFAULT_PHONE_FIELD Then
.Value = ""
End If
End With
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
Private Sub HomePhoneNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo ErrorHandler
With Me.HomePhoneNumber
If Len(.Value) = 0 Then
.Value = DEFAULT_PHONE_FIELD
End If
End With
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
Private Sub HomePhoneNumber_AfterUpdate()
On Error GoTo ErrorHandler
' format phone number
With Me.HomePhoneNumber
If Len(.Value) = 10 Then
.Value = Format(.Value, "(###) ###-####")
Else
.SetFocus
.SelStart = 0
.SelLength = Len(.Value)
End If
End With
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
The event handlers for the date field are similar. The Initialize event for the Userform sets up the fields when the form loads:
With Me
.HomePhoneNumber.Value = DEFAULT_PHONE_FIELD
.TodaysDate.Value = DEFAULT_DATE_FIELD
End With
End Sub
The sequence is like this: when the form loads, the fields are made to look like date or phone fields. When a field is entered, if it only contains the pseudo field information, clear it out to prepare for data entry. If the field is left (i.e. the user tabs to the next field), put the default information back in. If something is typed into the field, but it's not a phone number, highlight the field (I'm a big believer in not interrupting the user, but giving them a visual clue that they didn't enter the information correctly).
I created a userform so you can see these fields in action. The download link is below.
Date and phone fields sample workbook (visit the site to download)
(2 votes, average: 4.50 out of 5)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Free Book Giveaway
Next Post: Updated VBA Backup code
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].