Another Userform Format Question

[robfo0]

Hi everyone,

I have a problem. I have a userform in which users input a phone number into one of the fields. the users have not been inputting the number with the correct format (i would like it to be (123) 456-7890 ).

Juan gave me the code:
Private Sub ContractorPhone_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
ContractorPhone = Format(ContractorPhone, "(###) ###-####")
End Sub

and it works, EXCEPT when the user inputs the number like this: 123-456-7890
In this case, the number does not change to the correct format. How can i fix this? Is there a way to replace the - with a "" then i can format it? Ive tried messing around with it, but havent figured it out.

Thanks for any help

[Tom Morales]

robfo0 -
Here's a Rube Goldberg string manipulation I cobbled together. It reduces the input to a ten-character string, and then reformats it in the desired way.

Private Sub CommandButton1_Click()
'identify all the characters we want to purge
ws = Array("-", " ", "(", ")")
telno = TextBox1.Value
line10:
lt = Len(telno)
If lt = 10 Then GoTo line20 'you've got only digits
For Each w In ws 'looking for all spare characters
line11:
x = InStr(1, telno, w, 1)
If x = 0 Then GoTo line12 'no more of that character
front = Left(telno, x - 1)
back = Right(telno, lt - x)
telno = front & back
GoTo line10
line12:
Next
line20:
areacode = "(" & Left(telno, 3) & ") "
nexno = Mid(telno, 4, 3) & "-"
lasno = Right(telno, 4)
telno = areacode & nexno & lasno
End Sub

Tom