Create Input Mask in UserForm with Excel VBA

To create an input mask in a UserForm using Excel VBA, the goal is to ensure that the user enters data in a specific format (e.g., phone numbers, dates, etc.). Below is a detailed step-by-step guide on how to implement this in Excel VBA.

Step 1: Insert a UserForm

1. Open your Excel workbook.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
3. In the VBA editor, click Insert in the menu and choose UserForm. This will create a new UserForm where you can add controls.

Step 2: Design the UserForm

1. In the newly created UserForm, you can add controls such as TextBox, CommandButton, Label, etc.
2. To add an input mask, you’ll need a TextBox where the user will input their data (e.g., a phone number or date).
3. You can also add a CommandButton to submit or process the data entered in the UserForm.

For example, for a phone number input mask, you could design it like this:

• Place a Label that says « Enter Phone Number: »
• Place a TextBox to allow users to enter the phone number.
• Place a CommandButton labeled « Submit » to process the input.

Step 3: Add Code to the UserForm

To create an input mask, you’ll use the TextBox’s KeyPress or Change event to restrict the input and format it correctly. Below is an example of a phone number input mask, where the format should be (XXX) XXX-XXXX.

Code for the Phone Number Input Mask

In the VBA editor:

1. Right-click on the TextBox you placed for phone number input.
2. Choose View Code and enter the following VBA code:

Private Sub TextBox1_Change()
    Dim Text As String
    Text = TextBox1.Text
    ' Remove non-numeric characters
    Text = Replace(Text, "(", "")
    Text = Replace(Text, ")", "")
    Text = Replace(Text, "-", "")
    Text = Replace(Text, " ", "")
    ' Add input mask: (XXX) XXX-XXXX
    If Len(Text) <= 3 Then
        TextBox1.Text = "(" & Text
    ElseIf Len(Text) <= 6 Then
        TextBox1.Text = "(" & Mid(Text, 1, 3) & ") " & Mid(Text, 4, Len(Text))
    Else
        TextBox1.Text = "(" & Mid(Text, 1, 3) & ") " & Mid(Text, 4, 3) & "-" & Mid(Text, 7, 4)
    End If
    ' Ensure cursor stays at the correct position
    TextBox1.SelStart = Len(TextBox1.Text)
End Sub

Explanation of the Code:

• The TextBox1_Change event triggers every time the user types something in the TextBox1.
• We remove all non-numeric characters (i.e., (, ), -, and spaces) using the Replace function.
• We then format the text as the user types, adding parentheses and a hyphen at the appropriate places.
• Finally, the SelStart property ensures that the cursor stays at the end of the text box as the user types.

Step 4: Test the UserForm

1. Close the code window and return to the UserForm.
2. To test the form, press F5 in the VBA editor to run the form.
3. Try typing in the TextBox. The input will automatically be formatted into the (XXX) XXX-XXXX mask.

Example Output:

When testing the form, as the user types the phone number, the TextBox will automatically adjust to show the format like this:

• If the user types 1234567890, it will appear as (123) 456-7890.
• If they type 1, it will appear as (1.
• As more digits are added, it continues to format them correctly.

Further Enhancements:

• You can modify the input mask for other types of data, such as credit card numbers, social security numbers, or dates, by adjusting the formatting logic in the TextBox1_Change event.
• You can also add validation to ensure that the user enters a valid number of digits (e.g., 10 digits for a phone number).