Results 1 to 3 of 3

Thread: Number validation in Text Boxes VBA

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi All,

    Here is a way to validate text box allowing only numbers.

    Code:
    Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
        
        Dim strDecimalSprtr     As String
        Dim lngDSAsc            As Long
        Dim strTBValue          As String
        Dim lngDSCount          As Long
        Dim lngMSCount          As Long
        
        strDecimalSprtr = Application.International(3) 'xlDecimalSeparator
        lngDSAsc = Asc(strDecimalSprtr)
        Const lngMinusSign      As Long = 45
        
        Select Case KeyAscii
            'if you don't want to include minus sign, comment the following 3 lines
            Case lngMinusSign
                strTBValue = Me.TextBox1.Text
                If Len(strTBValue) Then KeyAscii = 0
            'if you don't want to include decimal separator, comment the following 4 lines
            Case lngDSAsc
                strTBValue = Me.TextBox1.Text
                lngDSCount = Len(strTBValue) - Len(Replace(strTBValue, strDecimalSprtr, vbNullString))
                If lngDSCount = 1 Then KeyAscii = 0
            Case 48 To 57
            Case Else
                KeyAscii = 0
                MsgBox "Only numbers allowed", vbInformation, "ExcelFox.com"
        End Select
        
    End Sub
    Note: adjust the Text Box name highlighted in red

    I hope this is useful to you
    Last edited by Admin; 05-17-2012 at 11:18 AM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Three things...

    1. In order to permit floating point values to be typed in, your code must allow the user to type in a decimal point character. If that is all the user types in, then the TextBox will not contain a number, rather, it will contain just a single non-digit character... the decimal point character. So, any code that makes use of the value in the TextBox will have to test for it containing just a single decimal point character before attempting to use it as if it were a numeric value.

    2. Your code, as written, will not prevent the user from copy/pasting non-digits, non-decimal points into the TextBox, so one cannot count on the contents of the TextBox being a guaranteed numeric value (once a single decimal point character has been ruled out).

    3. I assume you missed this posting of my in response to another message in this forum... http://www.excelfox.com/forum/f2/sub...-306/#post1039. The second code box contains routine that restricts the entry to a TextBox to a floating point number and overcomes the problem outlined in #2 above; however, it suffers from the same problem as your code does as outlined in #1 above. As written, the indicated code does not pop up the MessageBox the way your code does, but that is easily fixed by replacing the Beep command with your MessageBox line.
    Last edited by Rick Rothstein; 05-17-2012 at 01:52 PM.

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Rick,

    THanks for the input. Yeah, I missed your other post. Anyway, in normal case (I assume, no floating point entry, no copy paste), this would work. And yes, you think more advanced than me
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Wrap Text On Spaces Up To A Maximum Number Of Characters Per Line
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 15
    Last Post: 12-20-2016, 09:47 AM
  2. Extract Number From Alphanumeric Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 10
    Last Post: 09-11-2013, 10:14 PM
  3. Data Validation For Selecting Date And Week Number
    By paul_pearson in forum Excel Help
    Replies: 8
    Last Post: 06-16-2013, 05:07 AM
  4. VBA Validation List set
    By xander1981 in forum Excel Help
    Replies: 3
    Last Post: 02-15-2013, 04:07 PM
  5. Replies: 2
    Last Post: 09-25-2012, 01:30 AM

Tags for this Thread

Posting Permissions

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