Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: Allow Numeric Entries Only in a TextBox

  1. #1
    Join Date
    3rd February 2006
    Location
    St. Louis, Missouri USA
    Posts
    161

    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:
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  3. #3
    Join Date
    3rd February 2006
    Location
    St. Louis, Missouri USA
    Posts
    161

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    3rd February 2006
    Location
    St. Louis, Missouri USA
    Posts
    161

    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:
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Re: Allow Numeric Entries Only in a TextBox

    VB:
     '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)

  6. #6
    Join Date
    3rd February 2006
    Location
    St. Louis, Missouri USA
    Posts
    161

    Re: Allow Numeric Entries Only in a TextBox

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    3rd February 2006
    Location
    St. Louis, Missouri USA
    Posts
    161

    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
    VB:
    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:
    VB:
    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:
    VB:
    Private Sub tbExchange_Change() 
        OnlyNumbers 
        If ISLIKE(tbExchange, "###") Then 
            tbLastFour.SetFocus 
        End If 
    End Sub 
    
    
    The only difference between the two is
    VB:
    obAreaCodeOther.Value = True 
    tbAreaCode.Value = tbAreaCodeOther 
    
    
    so, I'm strongly suspecting the second line, but my efforts to correct it haven't been successful.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    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)

  9. #9
    Join Date
    3rd February 2006
    Location
    St. Louis, Missouri USA
    Posts
    161

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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    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 at 05:39. Reason: Another thing to think about...
    Sub All_Macros(Optional control As Variant)

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Automatically Change Numeric Entries To New Values
    By greengoose in forum EXCEL HELP
    Replies: 4
    Last Post: August 31st, 2007, 13:55
  2. Textbox Numeric Validation
    By toecutter in forum EXCEL HELP
    Replies: 2
    Last Post: December 6th, 2006, 16:57
  3. Round Up Numeric TextBox Entries
    By toecutter in forum EXCEL HELP
    Replies: 12
    Last Post: October 1st, 2006, 13:41
  4. TextBox Numeric Validation
    By Simon Bianchi in forum EXCEL HELP
    Replies: 1
    Last Post: August 10th, 2006, 17:09
  5. Data Validation for Alpha-Numeric Entries
    By cabuto in forum EXCEL HELP
    Replies: 4
    Last Post: July 22nd, 2006, 00:58

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