CODEWITHSUNDEEP
excelvba
Eatate
Eatate

Reputation: 13

Do not allow decimal numbers in UserForm TextBox - VBA

How to check for only Whole numbers ? no decimals allowed.

'Below is the code to check for numbers
For j = 1 To 59
    If Me.Controls("TextBox" & j).Value = "" Then
    Else
        If Not IsNumeric(Me.Controls("TextBox" & j).Value) Then
        MsgBox "only numbers are allowed"
        Cancel = False
        Exit Sub
        End If
    End If
Next j

Upvotes: 1

Views: 577

Answers (2)

Brian M Stafford
Brian M Stafford

Reputation: 8868

I think it is a better user experience to check as the user types not after the fact. Try something like this:

Option Explicit

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
   If Not GoodKeyPress(KeyAscii) Then KeyAscii = 0
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
   If Not GoodKeyPress(KeyAscii) Then KeyAscii = 0
End Sub

Private Function GoodKeyPress(ByVal KeyAscii As MSForms.ReturnInteger) As Boolean
   If KeyAscii = 46 Then MsgBox "No decimal allowed"

   'allow only numbers, negative sign and backspace
   If (KeyAscii >= 48 And KeyAscii <= 57) Or KeyAscii = 45 Or KeyAscii = 8 Then GoodKeyPress = True
End Function

Upvotes: 3

VBasic2008
VBasic2008

Reputation: 54807

Is String Digits?

A Quick Fix

' Below is the code to check for numbers
Dim j As Long
Dim cString As String

For j = 1 To 59
    cString = Me.Controls("TextBox" & j).Value
    If Len(cString) = 0 Then
        'MsgBox "At least one digit is necessary!", vbExclamation
    ElseIf Not (cString Like String(Len(cString), "#")) Then
        MsgBox "Only digits are allowed!", vbCritical
        Cancel = False ' ?
        Exit Sub
    Else ' valid input
    End If
Next j

A Function

  • The If statement in the previous code is 'splitting' the following function's two operations.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a boolean indicating whether all characters of a string
'               are digits.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IsDigits(ByVal S As String) As Boolean
    If Len(S) > 0 Then IsDigits = S Like String(Len(S), "#")
End Function

Upvotes: 0

Related Questions