Announcement

Collapse
No announcement yet.

Format/Validate UserForm TextBox Control For Phone Numbers

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

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

    Code:
    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 "(###)###-####")

  • #2
    Re: Formatting Multiple Textboxes On A Userform For Data Entry

    I use this code for that situation.
    Code:
    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, 15:07. Reason: Edited Code

    Comment


    • #3
      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
      Code:
      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
      Code:
      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, 05:41. Reason: Auto Merged Doublepost

      Comment


      • #4
        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:
        Code:
        Userform1.txtPhone.Text = Format(onlyNumerals(Range("A1")), "(000)000-0000")

        Comment


        • #5
          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, 02:13. Reason: Auto Merged Doublepost

          Comment

          Working...
          X