Announcement

Collapse
No announcement yet.

Allow Numeric Entries Only in a TextBox

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Allow Numeric Entries Only in a TextBox

    Hello All,

    I'm trying to do some data validation on my UserForm. I'd like to disallow any non-numeric keystroke in my textbox as soon as it is made. If the user enters a non-numeric symbol or a letter, like "D" or "舵", I want the validation to immediately prevent the keystroke from being excepted.

    An Example: If the user attempts to enter "12D46C5", the textbox records "12", the "D" is immediately rejected, the "46" is included, the "C" does not register, and the "5" is entered - so that the text displayed is "12465" before the Before_Update Event fires. If this is working properly, the keys displaying letters for example, would appear to be dead for entering text, having no affect on this textbox. (Although they would still work for navigation like an Alt+N accelerator.)

    Currently, I'm using a custom function ISLIKE to do data validation, but it only evaluates the whole string. I would like to modify it somehow so that I can evaluate each character as it is typed to make sure it is numeric. The function looks like this:
     Function ISLIKE(text As String, pattern As String) As Boolean
    ' Returns true if the first argument is like the second
    If text Like pattern Then ISLIKE = True _
    Else ISLIKE = False
    End Function
    I really appreciate any suggestions on how to revise the function to validate each keystroke as it comes in. Or, if there is another/better way of doing it - I'm interested. Thank you in advance for helping.

  • #2
    Re: Allow Numeric Entries Only in a TextBox

    Hi,

    See this page
    Validating UserForm TextBox to Only Accept Numbers

    Comment


    • #3
      Re: Allow Numeric Entries Only in a TextBox

      Too Cool! Thanks, Dave!

      I had searched and viewed over a hundred threads trying to find that info. I didn't know there were other places on the site to look. Now that I looked the homepage over better, it was kind of obvious - the whole
      "Search Site for....Excel Help".
      Thanks again!

      Comment


      • #4
        Re: Allow Numeric Entries Only in a TextBox

        Okay. I feel like Pinky from Pinky and The Brain. For those of you not familiar with Pinky, he's an idiot lab rat in a cartoon.

        I'm trying to adjust this code slightly, so that instead of removing the whole string from the text box, it only removes the disallowed character. Currently, if I type "123D", it will remove the whole string when I make the "D" keystroke. I would like it to leave the "123". This should be really simple, but I can't make it work. I'm like, "Duuhh, I don no Brain. Narf!"

        Here is the code from the link above:
         Private Sub OnlyNumbers() 
        With Me.ActiveControl
        If Not IsNumeric(.Value) And .Value <> vbNullString Then
        ' MsgBox "Sorry, only numbers allowed"
        .Value = vbNullString
        End If
        End With
        End Sub
        Please help a tooned out lab rat in need.

        Comment


        • #5
          Re: Allow Numeric Entries Only in a TextBox

          Code:
          'assuming...
          Private Sub TextBox1_Change()
              Call OnlyNumbers
          End Sub
          
          'then...
          Private Sub OnlyNumbers()
              test$ = ActiveControl.Value
              With Me.ActiveControl
                  For i = 1 To Len(test$)
                      If Not IsNumeric(Mid(test$, i, 1)) Then
                          .Value = Left(test$, Len(test$) - 1)
                      End If
                  Next i
              End With
          End Sub
          Sub All_Macros(Optional control As Variant)

          Comment


          • #6
            Re: Allow Numeric Entries Only in a TextBox

            Perfect!!! Idiot lab rats everywhere are greatful. Thank you! Thank you! Thank you!

            Comment


            • #7
              Re: Allow Numeric Entries Only in a TextBox

              Okay. I've created some kind of conflict here. The code Aaron gave me works perfectly when it is the only item in the Change event. But I'm also using
                   If ISLIKE(tbAreaCodeOther, "###") Then
              obAreaCodeOther.Value = True
              tbAreaCode.Value = tbAreaCodeOther
              tbExchange.SetFocus
              End If
              to automatically move to the next field when the required entry has been made - where ISLIKE() is as defined in my post above and "tb" and "ob" denote textbox and obtion button. This also works by itself. When I put the two together:
               Private Sub tbAreaCodeOther_Change()
              OnlyNumbers
              If ISLIKE(tbAreaCodeOther, "###") Then
              obAreaCodeOther.Value = True
              tbAreaCode.Value = tbAreaCodeOther
              tbExchange.SetFocus
              End If
              End Sub
              I get Run time error '438' - Object doesn't support this property or method.

              I'm using OnlyNumbers together with similar validation in other text boxes with no problems:
               Private Sub tbExchange_Change()
              OnlyNumbers
              If ISLIKE(tbExchange, "###") Then
              tbLastFour.SetFocus
              End If
              End Sub
              The only difference between the two is
                       obAreaCodeOther.Value = True
              tbAreaCode.Value = tbAreaCodeOther
              so, I'm strongly suspecting the second line, but my efforts to correct it haven't been successful.

              Comment


              • #8
                Re: Allow Numeric Entries Only in a TextBox

                ...and why program what "Tab Stop" and "Tab Index" are designed for?
                Sub All_Macros(Optional control As Variant)

                Comment


                • #9
                  Re: Allow Numeric Entries Only in a TextBox

                  Keystoke savings.

                  This form will be used to make hundreds of thousands of entries. Having the focus move to the next control without pressing the TAB button will reduce total keystrokes per entry from the 42-45 range to the 30-33 range. I'm thinking it could cut data entry time by 25%.

                  Comment


                  • #10
                    Re: Allow Numeric Entries Only in a TextBox

                    ...um yeah... but the focus moves to the next input on the enter button as well.

                    Is it that you are wanting it to detect a certain number string length and automatically move without enter or tab keystrokes?

                    edit: Something else to consider... if you're data entry people are number-keypad-entry trained (like most accountants) you're not saving anywhere near 25% on the time, if anything they may even curse you because their fingers automatically hit that enter key every time. Just a thought...
                    Last edited by Aaron Blood; February 9th, 2006, 05:39. Reason: Another thing to think about...
                    Sub All_Macros(Optional control As Variant)

                    Comment


                    • #11
                      Re: Allow Numeric Entries Only in a TextBox

                      Originally posted by Aaron Blood
                      Is it that you are wanting it to detect a certain number string length and automatically move without enter or tab keystrokes?
                      That's exactly it.

                      As far as number-keypad-entry trained, doubt it. Some are faster than others, but were not talking accountants. You bring up a good point about automatically hitting the enter key though. In the outside chance that this is later done by people that are number-keypad-entry trained, (wow, wouldn't that be great), I would need to consider that. Maybe disabling the Enter or something so that it still works for the trained and the untrained. I don't know, but right now, I've got untrained.

                      Comment


                      • #12
                        Re: Allow Numeric Entries Only in a TextBox

                        Well, OK then...

                        I would definitely include a checkbox on the form to turn it on/off.

                        And your 25% keystroke savings of course assumes no wrong number keys are pressed. But OK; they turn it on/off if they want to use it, that's cool.

                        What if you just added a test for string length and if it equals a certain number send the keystroke! This way you don't override the Tab properties with code AND you achieve the saved keystroke goal.

                        Code:
                        Private Sub TextBox1_Change()
                            Call OnlyNumbers
                            If Len(Me.ActiveControl) = 5 And Me.CheckBox1 Then
                                Application.SendKeys "~"
                            End If
                        End Sub

                        Additionally, I might consider putting that string length test inside the OnlyNumbers sub so you don't have to repeat it for every control. Maybe...

                        Code:
                        Private Sub TextBox1_Change()
                            Call OnlyNumbers
                        End Sub
                        
                        Private Sub OnlyNumbers()
                            test$ = ActiveControl.Value
                            With Me.ActiveControl
                                For i = 1 To Len(test$)
                                    If Not IsNumeric(Mid(test$, i, 1)) Then
                                        .Value = Left(test$, Len(test$) - 1)
                                    End If
                                Next i
                                If Len(.Value) = 5 And Me.CheckBox1 Then
                                    Application.SendKeys "~"
                                End If
                            End With
                        End Sub
                        Granted, I'm making an assumption in this case that the string length is the same for all controls. If it's changing, you could pass a value from each call to the OnlyNumbers sub. That would still be cleaner than duplicating code in all the controls.
                        Last edited by Aaron Blood; February 9th, 2006, 06:17.
                        Sub All_Macros(Optional control As Variant)

                        Comment


                        • #13
                          Re: Allow Numeric Entries Only in a TextBox

                          All great suggestions.

                          I really like the checkbox idea. Incidentally, I see you're right about the 25% time savings. I based that soley on the number of keystrokes, but if I can save only 5%, it's still worth it.

                          What if you just added a test for string length and if it equals a certain number send the keystroke!

                          Additionally, I might consider putting that string length test inside the OnlyNumbers sub
                          Both great suggestions. However, the string length is not the same for the various controls, and I don't know how to "pass a value from each call to the OnlyNumbers sub".

                          Comment


                          • #14
                            Re: Allow Numeric Entries Only in a TextBox

                            To pass the values you'd set it up like this:

                            Code:
                            Private Sub TextBox1_Change()
                                Call OnlyNumbers(3)
                            End Sub
                            
                            Private Sub TextBox2_Change()
                                Call OnlyNumbers(5)
                            End Sub
                            
                            Private Sub TextBox3_Change()
                                Call OnlyNumbers(4)
                            End Sub
                             
                            Private Sub OnlyNumbers(MaxLen As Integer)
                                test$ = ActiveControl.Value
                                With Me.ActiveControl
                                    For i = 1 To Len(test$)
                                        If Not IsNumeric(Mid(test$, i, 1)) Then
                                            .Value = Left(test$, Len(test$) - 1)
                                        End If
                                    Next i
                                    If Len(.Value) = MaxLen And Me.CheckBox1 Then
                                        Application.SendKeys "~"
                                    End If
                                End With
                            End Sub
                            Alternatively, if you wanted to be fancy, you could set the tag property of the control equal to the MaxLen value you'd like to use for the control. Then you're code for every control is the same...

                            Code:
                            Private Sub TextBox1_Change()
                                Call OnlyNumbers(Me.ActiveControl.Tag)
                            End Sub
                            
                            Private Sub TextBox2_Change()
                                Call OnlyNumbers(Me.ActiveControl.Tag)
                            End Sub
                            
                            Private Sub TextBox3_Change()
                                Call OnlyNumbers(Me.ActiveControl.Tag)
                            End Sub
                            Last edited by Aaron Blood; February 9th, 2006, 06:52.
                            Sub All_Macros(Optional control As Variant)

                            Comment


                            • #15
                              Re: Allow Numeric Entries Only in a TextBox

                              Sweeeeet.

                              By the way, I checked out your website. It rocks.

                              Comment

                              Working...
                              X