Announcement

Collapse
No announcement yet.

Format Phone Number In TextBox

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Format Phone Number In TextBox

    Didnt realy Know What to call this

    i have a form where you put a phone number
    and i want it to split the phone number
    in to 5 3 3 format
    where 07849657510
    becomes 07849 657 510
    as you enter it

    Code:
    Private Sub TextBox126_Change()
    
    
    If Len(TextBox126.Value) = 5 Then
    TextBox126.Value = TextBox126.Value & " "
    End If
    
    
    If Len(TextBox126.Value) = 9 Then
    TextBox126.Value = TextBox126.Value & " "
    End If
    
    
    End Sub
    i have this
    and it works
    until i try to delete past a break
    i know why this happens but how could i stop it

    or is there a better way of doing it??
    im new to vba so be gentle

  • #2
    Re: Form Textbox Code Splitting A Phone Number

    Try this:
    Code:
    Private Sub TextBox126_Change()
    
        Dim FormatString As String
        
        If Len(Replace(TextBox126.Value, " ", "")) = 5 Or Len(Replace(TextBox126.Value, " ", "")) = 8 Then Exit Sub
    
        For i = 1 To Len(Replace(TextBox126.Value, " ", ""))
            FormatString = FormatString & "#"
            If i = 5 Or i = 8 Then
                FormatString = FormatString & " "
            End If
        Next i
        
        TextBox126.Value = Format(Replace(TextBox126.Value, " ", ""), FormatString)
        
    End Sub

    Comment


    • #3
      Re: Form Textbox Code Splitting A Phone Number

      Code:
      Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
            TextBox1 = Format(TextBox1, "00000 000 000")
      End Sub

      Comment


      • #4
        Re: Format Phone Number In TextBox

        thanks dave
        top notch as always
        =D

        i didnt realy understand yours mike
        but i used dave's as it was shorter and easier to understand

        thanks guys
        im new to vba so be gentle

        Comment


        • #5
          Re: Format Phone Number In TextBox

          You might to check the textbox entry with LEN in the Exit Event too.

          Comment


          • #6
            Re: Format Phone Number In TextBox

            Originally posted by FRIEL
            thanks dave
            top notch as always
            =D

            i didnt realy understand yours mike
            but i used dave's as it was shorter and easier to understand

            thanks guys

            My code is not that complicted. It simply creates the format based on the number of characters in the textbox. That way, the phone numbers are formatted properly as they are being entered. I thought that's what you wanted.

            Comment


            • #7
              Re: Format Phone Number In TextBox

              hmm yea just tryed yours mike
              and it fixes the problem i have been having
              when i go over 11 digits it was putting the spaces further on in the string

              what i ment by complicated was that i dont understand it
              im not that experenced and i only know what i have learned form here and the excel help file

              i dont like to use code that i dont understand because it makes it harder to edit
              but i may make an exception in this case

              if possible could you explain in a little more depth, so i can learn somthing please?

              thanks allot guys
              im new to vba so be gentle

              Comment


              • #8
                Re: Format Phone Number In TextBox

                It's sort of a combination of what you started with, and the format code that Dave provided. You ultimately want to end up with Format(TextBox126.value, "00000 000 000") or Format(TextBox126.value, "##### ### ###")...either one will work fine, I chose to use "#". However, if you simply format all entries that way, I found that it does strange and unwanted things as you're typing in numbers. So, you have to create the format to match the number of digits being entered. In this loop:
                Code:
                    For i = 1 To Len(Replace(TextBox126.Value, " ", ""))
                        FormatString = FormatString & "#"
                        If i = 5 Or i = 8 Then
                            FormatString = FormatString & " "
                        End If
                    Next i
                for each digit in textbox126.value it will add a "#" to formatstring, with a space entered after digit 5 and digit 8. For example, if "123" is entered the format string will be "###"...and if "123456" is entered the format string will be "##### #".
                The resulting format string is then used in the last line of the code:
                Code:
                    TextBox126.Value = Format(Replace(TextBox126.Value, " ", ""), FormatString)
                The Replace function used throughout the subroutine removes any spaces in textbox.value created by previous formatting or typed by the user.

                At the beginning I added this line so that you could use backspace. It bails out at digits 5 and 8 so that the space isn't added continuously.
                Code:
                If Len(Replace(TextBox126.Value, " ", "")) = 5 Or Len(Replace(TextBox126.Value, " ", "")) = 8 Then Exit Sub

                Hope this helped.

                Comment

                Working...
                X