Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Format Phone Number In TextBox

  1. #1
    Join Date
    8th December 2007
    Location
    UK
    Posts
    56

    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

    VB:
    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. #2
    Join Date
    13th February 2008
    Posts
    74

    Re: Form Textbox Code Splitting A Phone Number

    Try this:
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    Re: Form Textbox Code Splitting A Phone Number

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

  4. #4
    Join Date
    8th December 2007
    Location
    UK
    Posts
    56

    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

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    Re: Format Phone Number In TextBox

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

  6. #6
    Join Date
    13th February 2008
    Posts
    74

    Re: Format Phone Number In TextBox

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th December 2007
    Location
    UK
    Posts
    56

    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

  8. #8
    Join Date
    13th February 2008
    Posts
    74

    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:
    VB:
    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:
    VB:
    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.
    VB:
    If Len(Replace(TextBox126.Value, " ", "")) = 5 Or Len(Replace(TextBox126.Value, " ", "")) = 8 Then Exit Sub 
    
    

    Hope this helped.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Format TextBox Date Entered As Whole Number
    By doshi_nilesh in forum EXCEL HELP
    Replies: 2
    Last Post: June 3rd, 2008, 06:46
  2. Replies: 4
    Last Post: April 11th, 2008, 10:43
  3. Textbox Number Format
    By abbeville in forum EXCEL HELP
    Replies: 4
    Last Post: September 16th, 2006, 17:10
  4. Format Textbox to Specific Number Format
    By tonestertb in forum EXCEL HELP
    Replies: 3
    Last Post: August 27th, 2006, 02:29
  5. Format Phone number as it is being entered
    By Dave Godfrey in forum EXCEL HELP
    Replies: 5
    Last Post: March 8th, 2006, 14:01

Bookmarks

Posting Permissions

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