VBA Textbox For Phone Numbers

[CokeOrCrack]

-In my Userform, I have a textbox where a user is to input their phone number
-Upon clicking the Submit command button in the Userform, the phone number is then inputed into a table cell
-I want the Phone Number column to be formatted: (###) ###-####
Herein lies the problem:
-The phone number data is copied from another source and its formatting is dependent on the applicant. There are a wide range of ways applicants format their phone numbers

Question:
Is there a way to make it so that no matter what format an applicant submits their phone number, the phone number cell in the worksheet is correctly formatted (###) ###-####?

The main roadblock appears to be that textboxes are text only, no numbers. I have the worksheet phone number column already formatted (###) ###-####.

Thanks

OJ

 

[NoSparks]

Have you tried deleting everything but numeric digits from whatever is entered into the Userform textbox and going from there ?
even formatting as required when leaving the textbox.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim telnum As String, i As Integer, newtelnum As String
    
telnum = Trim(Me.TextBox1.Value)
For i = 1 To Len(telnum)
    If Mid(telnum, i, 1) Like "[0-9]" Then
        newtelnum = newtelnum & Mid(telnum, i, 1)
    End If
Next i

Select Case Len(newtelnum)
    Case Is < 10
    MsgBox "Not enough numbers entered."
    Cancel = True
    Exit Sub
    
    Case Is > 10
    MsgBox "Too many numbers entered."
    Cancel = True
    Exit Sub
End Select

'got here so exactly 10 digits entered
'newtelnum = "(" & Left(newtelnum, 3) & ") " & Mid(newtelnum, 4, 3) & "-" & Right(newtelnum, 4)
Me.TextBox1.Value = newtelnum

End Sub

 

[CokeOrCrack]

Applicant phone numbers are copied and pasted directly from their application. Some applicants submit 9 digit strings while others input it as (###) ###-#### and various other ways. I have no problem with 9 digit strings, because I multiply the textbox input by 1 and get a number rather than text. This does not work when there are parenthesis and spaces.

.Range(1, 11) = Me.tbPhone * 1

I need the textbox to be able to accept both 9 digit strings as well as formatted phone numbers inputted.

I will give you code a try.

 

[CokeOrCrack]

WOW! This works really well. If you don't mind me asking, I don't want too much of your time, but can you break your code down a little bit so I can understand it better?

As an example of what I see, I typed in a correctly formatted phone number (111) 111-1111 and upon pressing tab (prior to pressing the submit command button), it immediately formatted it to a 9 digit string (exactly what I need).