Number validation in Text Boxes VBA

[Admin]

Hi All,

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

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

[Rick Rothstein]

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.

[Admin]

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