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

CodePudding user response：

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)
   'allow only numbers and backspace
   If Not (KeyAscii >= 48 And KeyAscii <= 57) And Not KeyAscii = 8 Then KeyAscii = 0
End Sub

CodePudding user response：

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