Another Userform Format Question

robfo0

Active Member
Joined
Feb 19, 2002
Messages
281
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top