Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Format/Validate UserForm TextBox Control For Phone Numbers

  1. #1
    Join Date
    4th April 2008
    Posts
    17

    Format/Validate UserForm TextBox Control For Phone Numbers

    Hi all, So I use this userform that has a bunch of different text boxes in it and I've looked around for some code to change some of the textbox formats to currency ("$#,##0.00") and some to date ("MM/DD/YYYY), and anouther one to a Phone number ("(###)###-####") I found this code to change the format of some of the textboxes, but unfortunatlly if you enter it in more then once the userform will bug out and not work correctlly. I haven't been able to find the code the change all my textboxs yet, if anyone could point me in the right direction that would be great. this is the code i was using.

    VB:
    Private Sub txtamount_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
        txtamount = Format(txtamount, "$#,##0.00") 
    End Sub 
     
    Private Sub txtamount_Change() 
        OnlyNumbers 
    End Sub 
     
     
     
    Private Sub OnlyNumbers() 
         
        If TypeName(Me.ActiveControl) = "txtamount" Then 
            With Me.ActiveControl 
                If Not IsNumeric(.Value) And .Value <> vbNullString Then 
                    MsgBox "Sorry, only numbers allowed" 
                    .Value = vbNullString 
                End If 
            End With 
        End If 
         
    End Sub 
    
    
    The TextBoxe's names are as follows: "txtamount", "txtfee", 'txtaccttotal", "txtpayment", "txtacctbalance" (these all are "$#,##0.00"), "txtdate", "txtpdate" (These two are "MM/DD/YYYY"), "txtage" (Is "#,##0), "txtphone" (Is "(###)###-####")

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd April 2007
    Posts
    3,417

    Re: Formatting Multiple Textboxes On A Userform For Data Entry

    I use this code for that situation.
    VB:
    Private Sub txtPhone_AfterUpdate() 
        With Me.txtPhone 
            .Text = Format(onlyNumerals(.Text), "(000)000-0000") 
        End With 
    End Sub 
     
    Function onlyNumerals(inputString As String) As String 
        Dim i As Long 
        For i = 1 To Len(inputString) 
            If Mid(inputstring,i,1) Like "[0-9]" 
            onlyNumerals = onlyNumerals & Mid(inputString, i, 1) 
        End If 
    Next i 
    End Function 
    
    
    The onlyNumerals function is actualy in a normal module and could be improved for a Windows version that supports a Dictionary object.
    Last edited by mikerickson; April 10th, 2008 at 15:07. Reason: Edited Code

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th April 2008
    Posts
    17

    Re: Format/Validate UserForm TextBox Control For Phone Numbers

    Thanks, That works great fro the data entry. I have one userform that will pull up an account information but for some reason when it pulls the information the
    VB:
    Private Sub txtPhone_AfterUpdate() 
        With Me.txtPhone 
            .Text =  Format(onlyNumerals(.Text), "(000)000-0000") 
        End With 
    End Sub 
    
    
    doesn't cause the information that is pulled into the userform to change to the correct format. i've also tried
    VB:
    Private Sub txtphone_Change(ByVal Cancel As MSForms.ReturnBoolean) 
        txtphone = Format(txtphone, "(###)###-####") 
    End Sub 
    
    
    this code will bring up the information in the correct format but if you try to change it, it will bug out on you.
    Auto Merged Post Until 24 Hrs Passes;

    let me clairify a bit, If you change the value's of the textboxes with the above formating the textbox will not allow you to enter the information correctly, it will take the first number you enter and apply the format to it, and not allow you to enter in any following numbers
    Last edited by Phoenixdown; April 11th, 2008 at 05:41. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd April 2007
    Posts
    3,417

    Re: Format/Validate UserForm TextBox Control For Phone Numbers

    Filling a textbox will fire the Change event, not the After Update.
    If you are putting cell values directly, perhaps something like this:
    VB:
    Userform1.txtPhone.Text = Format(onlyNumerals(Range("A1")), "(000)000-0000") 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th April 2008
    Posts
    17

    Re: Format/Validate UserForm TextBox Control For Phone Numbers

    [OUTSIDE OF THREAD TITLE]
    Auto Merged Post Until 24 Hrs Passes;

    [OUTSIDE OF THREAD TITLE]?

    the information contained in my last post what pertaining to a question related to this thread discussion.

    if there are any moderators who know why my post was deleted could they please explain.
    Last edited by Phoenixdown; April 12th, 2008 at 02:13. Reason: Auto Merged Doublepost

    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 Phone Number In TextBox
    By FRIEL in forum EXCEL HELP
    Replies: 7
    Last Post: May 31st, 2008, 00:04
  2. UserForm Textbox Format For Numbers
    By gsandy in forum EXCEL HELP
    Replies: 6
    Last Post: April 23rd, 2008, 12:37
  3. Validate Cells For Phone Numbers With Conditions
    By boydio in forum EXCEL HELP
    Replies: 1
    Last Post: December 14th, 2007, 13:27
  4. Validate TextBox for Phone Numbers
    By Zzyzzyth in forum EXCEL HELP
    Replies: 3
    Last Post: August 17th, 2006, 14:21
  5. Validate Phone Numbers & Zip codes with VBA
    By Phil in forum EXCEL HELP
    Replies: 9
    Last Post: April 2nd, 2003, 22:54

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