Format TextBox for Phone Number with Excel VBA (A Step-by-Step Guide)

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.

Excel VBA to Format Textbox Phone Number


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.

Excel VBA to Format Textbox Phone Number

Read More:Β How to Write Phone Number in Excel (Every Possible Way)


πŸ“Œ 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.

Excel VBA to Format Textbox Phone Number

Read More:Β Excel Formula to Change Phone Number Format (5 Examples)


πŸ“Œ 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.

Excel VBA to Format Textbox Phone Number

  • Then press CTRL+S and then ALT+Q to save and return to the worksheet.

Excel VBA to Format Textbox Phone Number


πŸ“Œ 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.

Excel VBA to Format Textbox Phone Number

Read More: [Solved!]: Excel Phone Number Format Not Working (4 Solutions)


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.

Conclusion

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.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo