Format Phone number as it is being entered

Dave Godfrey · Mar 8th 2006

First I want to thank yall "Texas Lingo" for your help in the past.
Here is my problem. I have a Form with a TextBox that phone numbers will be entered into. I would like the code to automatically format the number as they enter the 10 digits. They enter XXXXXXXXXX and the Textbox displays (XXX) XXX-XXXX. I am trying to get the "(" , ")" , and "-" to be added as the numbers are entered. Any help would be appreciated.

Dave Hawley · Mar 8th 2006

Re: Format Phone number as it is being entered

Hi Dave

I would use the Exit Event to format after typing.

Code

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1 = Format(TextBox1, "(0) 000-000")
End Sub

Dave Godfrey · Mar 8th 2006

Re: Format Phone number as it is being entered

I was thinking of that but would rather have it add the formatting as they type. This would give them feedback as they enter the number. I have seen it done but havent been able to get it to work yet. Have been trying "Change" and "Key Up". I guess I am looking for another way to look at the problem to solve it.

Dave Godfrey · Mar 8th 2006

Re: Format Phone number as it is being entered

I figured it out. If anyone is interested, here is the code.

Code

Private Sub TextBox1_Change()
    Dim Phone As String
    Dim Length As Integer
        Phone = TextBox1.Text
        Length = Len(Phone)
        Select Case Length
        
        Case 1
        TextBox1.Text = Format(TextBox1, "(0")
        Case 4
        TextBox1.Text = Format(TextBox1, "(000") & ") "
        Case 9
        TextBox1.Text = Format(TextBox1, "(000)000") & "-"
        End Select
End Sub

Display More

Dave Hawley · Mar 8th 2006

Re: Format Phone number as it is being entered

Thanks for sharing Dave.

max_lux · Mar 8th 2006

Re: Format Phone number as it is being entered

Nice job Dave, I've wondered about code that does such a thing.

Here it is for with the leading country number ie for North America +1 (000) 867-5309

Would have to be formatted for Europe differently

Max Length (17)

Code

Private Sub TextBox1_Change()
    Dim Phone As String
    Dim Length As Integer
    Phone = TextBox1.Text
    Length = Len(Phone)
    Select Case Length
         
    Case 1
        TextBox1.Text = Format(TextBox1, "0 (")
    Case 3
        TextBox1.Text = Format(TextBox1, "0(0")
    Case 6
        TextBox1.Text = Format(TextBox1, "(000") & ") "
    Case 11
        TextBox1.Text = Format(TextBox1, "(000)000") & "-"
    End Select
End Sub

Display More

Now to add isNumeric code, and figure out how to allow the user to backspace and correct without having to select everything

Format Phone number as it is being entered

Participate now!

Share