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/subclassing-textbox1-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 :)