Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Another Userform Format Question

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Richland, Washington
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •