Md. Shamim Reza
This article illustrates how to use Excel VBA to format textbox for phone number. The following image highlights the purpose of this article. Have a quick look through it to learn how to do that.
How to Format TextBox for Phone Number in Excel VBA: A Step-by-Step Guide
Imagine you want to enter phone numbers in a TextBox in an Excel UserForm. You want to format the phone number as xxx-xxx-xxxx while you are typing. And then, you want to enter the phone number directly into your worksheet. Then follow the steps below.
📌 Step-1: Create a UserForm and Add TextBox with Proper Format for Phone Number
- First, press ALT+F11 to open the VBA window. Then select Insert >> UserForm, as shown in the following picture.
- After that, a new UserForm will be created, as shown below. Change the caption of the form from Properties. Then click on the form. This will make the Toolbox visible. Next, click on the TextBox icon in the Toolbox.
- Then drag the mouse to select the area for the TextBox as shown in the following picture.
- After that, a new TextBox will be created, as shown below. Change MaxLength to 12 from Properties. You can also change other properties of the TextBox as required.
- Now, add 3 command buttons by clicking on the CommandButton icon in the Toolbox. Then, change their names to SubmitButton, ResetButton, ExitButton, and captions to Submit, Reset, Exit, respectively.
Read More: How to Write Phone Number in Excel
📌 Step-2: Enter VBA Code in the UserForm to Format the Input Phone Number in the TextBox
- Now you can either right-click on the UserForm and select View Code or double-click on it.
- After that, you will see some auto-generated code as follows. Now select the code entirely.
- Then copy the following code using the copy button.
'Only Allow Numbers as Input in the TextBox
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii >= 48 And KeyAscii <= 57) Then
KeyAscii = 0
End If
End Sub
'Add Dash in TextBox after First 3 Digits
Private Sub AddDash1(Textbox As MSForms.Textbox)
Dim Dash As String
Dash = Left(Textbox.Text, 3) & "-" & Right(Textbox.Text, 0)
Textbox.Text = Dash
End Sub
'Add Dash in TextBox after First 6 Digits
Private Sub AddDash2(Textbox As MSForms.Textbox)
Dim Dash As String
Dash = Left(Textbox.Text, 7) & "-" & Right(Textbox.Text, 0)
Textbox.Text = Dash
End Sub
'Show Dashes in TextBox after 3rd & 6th Digits
Private Sub TextBox1_Change()
TextBox1 = UCase(TextBox1)
If TextBox1.TextLength = 3 Then
Call AddDash1(TextBox1)
End If
If TextBox1.TextLength = 7 Then
Call AddDash2(TextBox1)
End If
'Limit Input Submission to 10 Digit Phone Numbers Only
If Len(TextBox1.Value) = 12 Then
SubmitButton.Enabled = True
Else
SubmitButton.Enabled = False
End If
End Sub
'Submit the TextBox Entry
Private Sub SubmitButton_Click()
Call Submit
End Sub
'Activate Reset Command
Private Sub ResetButton_Click()
Call Reset
End Sub
'Exit from the UserForm
Private Sub ExitButton_Click()
Unload Me
End Sub- Now paste the copied code on top of the auto-generated code, as shown below. The comments in the code will help you to understand the purpose of each section of the code.
Read More: Excel Formula to Change Phone Number Format
📌 Step-3: Enter VBA Code in a Module to Show the UserForm Using Form Control Button in Worksheet
- After that, select Insert >> Module as follows to open a new blank module.
- Then copy the code given below.
'Show UserForm1 Using Form Control Button in Worksheet
Sub DisplayUserForm()
UserForm1.Show
End Sub
'Add TextBox Entries in the Worksheet
Sub Submit()
Dim wsh As Worksheet
Dim iRow As Long
Set wsh = Sheets("PhoneNumbers")
iRow = wsh.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
With wsh
.Cells(iRow, 2) = UserForm1.TextBox1.Value
End With
End Sub
'Reset TextBox
Sub Reset()
With UserForm1
.TextBox1.Value = ""
End With
End Sub- Now paste the copied code on the blank module as shown below.
- Then press CTRL+S and then ALT+Q to save and return to the worksheet.
📌 Step-4: Create a Form Control Button and Assign the Macro
- After that, select Insert >> Button (Form Control) from the Developer tab, as shown in the following picture.
- Then drag the mouse to specify the area for the button as shown below.
- After that, assign the DisplayUserForm macro to the form control button.
📌 Final Step: Click on the Form Control Button, Enter a Phone Number and Get the Formatted Output
- Then, the button will be visible, as shown below. You can change its’ name (Show Form) as desired.
- Now click on the Show Form button, and the UserForm will pop up as follows.
- Then, enter a phone number in the textbox, and it will be formatted automatically as follows.
- After that, click on the Submit button, and the phone number will be entered directly into the worksheet.
- The Reset button will allow you to clear the TextBox and enter another phone number.
- You can submit more phone numbers to enter them in the worksheet as follows.
Read More: [Solved!]: Excel Phone Number Format Not Working
Things to Remember
- You can use space inside double quotes (“ ”) instead of a hyphen (–) to format the phone number as xxx xxx xxxx.
- Right-clicking will allow you to go to the properties of each tool in the UserForm.
Download Practice Workbook
You can download the practice workbook from the download button below.
Conclusion
Now you know how to use Excel VBA to format textbox for phone numbers. Please let us know if this article has helped you to solve your problem. You can also use the comment section below for further queries or suggestions. Stay with us and keep learning.
