Use textboxes like date or phone number fields

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_PHONE_FIELD As String = "(   ) ___ - ____"
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:

Private Sub HomePhoneNumber_Enter()

  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:

Private Sub UserForm_Initialize()
  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 pseudo fields