Left Right Hyphen Imbedding Issue

  • Hello, All!

    I am trying to make a userform text box moreorless like you often see for phone numbers. If you start typing, once you get the area code in, it enters a hyphen. You type the next three it enters another. However, I am entering numbers where the last digit always needs to be separated, and then the preceding two while the first string can be varying lengths.

    That is:








    I've tried combinations of the Left, Right, Mid, but the problem with this approach is that starts to include the hyphen in the count. I've tried to think about how to access / store it within a string but that fell flat as well. This seemed the most promising, but maybe not.

    1. Private Sub CASTextBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    2. KeyAscii = KeyAscii * -CLng(Chr(KeyAscii) Like "[0-9]")
    3. Select Case Len(CASTextBox)
    4. Case Is > 3: CASTextBox = Left(CASTextBox, Len(CASTextBox) - 4) & "-" & _
    5. Mid(CASTextBox, Len(CASTextBox) - 4, 2) & "-" & _
    6. Right(CASTextBox, 2)
    7. End Select
    8. End Sub

    I've attached a workbook ( CAS Hyphens.xlsm ) just for a little playground and starting point apart from my greater machine.

    Thank you for any and all assist. If you would like to send me off with a different approach, I have now taken a short course (8.5 hours of video) on VBA and shouldn't be quite so dense if you try to explain it to me.

  • Try this, you will need another control on the userform because the code works as you move to another control

    1. Private Sub CASTextBox_AfterUpdate()
    2. With Me.CASTextBox
    3. If Not IsNumeric(.Value) Then
    4. .Value = Empty
    5. Exit Sub
    6. Else: .Value = Format(.Value, "#####-##-#")
    7. End If
    8. End With
    9. End Sub
  • You know, this works! I appreciate the new angle. I was thinking it would be nice for the user to be aware the hyphens were being added as they typed. Sometimes I get stuck on fancy versus functional. Thanks much, Roy. Super helpful.

  • Pleased to help.

    Post back if you need further help.

    Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.