Admin
05-17-2012, 10:57 AM
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 :)
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 :)