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.
Download Practice Workbook
You can download the practice workbook from the download button below.
Steps to Format a TextBox for Phone Number Using Excel VBA Codes
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.
📌 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.
📌 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.
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.
Now you know how to use Excel VBA to format textbox phone number. 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. Do visit our ExcelDemy blog to read more on excel. Stay with us and keep learning.