如何在VBA的UserForm文本框中限制仅输入整数（禁止小数）？

Got it, let's tweak your existing code to make sure those text boxes only accept whole numbers—no decimals allowed! Your current code checks if input is numeric, but that includes values like 3.14 which we need to block. Here are a few solid approaches to fix this:

1. Modify Your Existing Loop to Check for Integers #

This method builds on your current code but adds a check to ensure the numeric value has no decimal part. Also, note that your original Cancel = False was backwards—we need Cancel = True to stop invalid input and return focus to the problematic text box.

For j = 1 To 59
    Dim txtBox As MSForms.TextBox
    Set txtBox = Me.Controls("TextBox" & j)
    Dim txtVal As Variant
    
    txtVal = txtBox.Value
    If txtVal <> "" Then
        ' First check if input is numeric at all
        If Not IsNumeric(txtVal) Then
            MsgBox "Only numbers are allowed!"
            Cancel = True
            txtBox.SetFocus
            Exit Sub
        Else
            ' Check if the value is an integer (no decimal part)
            If CDbl(txtVal) <> CLng(txtVal) Then
                MsgBox "Only integers are allowed—no decimals!"
                Cancel = True
                txtBox.SetFocus
                Exit Sub
            End If
        End If
    End If
Next j

How this works: #

• CDbl(txtVal) converts the input to a double (handles numbers with decimals)
• CLng(txtVal) converts it to a long integer (truncates any decimal part)
• If the two values don't match, the input has a decimal component—we flag it as invalid.

2. Use Regular Expressions for Strict Validation #

Regex is great for precise input matching. This method ensures the input is strictly an integer (optionally allowing negative numbers if needed).

For j = 1 To 59
    Dim txtBox As MSForms.TextBox
    Set txtBox = Me.Controls("TextBox" & j)
    Dim txtVal As String
    
    txtVal = txtBox.Value
    If txtVal <> "" Then
        ' Use late binding for regex (no need to add references)
        Dim regex As Object
        Set regex = CreateObject("VBScript.RegExp")
        
        ' Pattern allows optional negative sign + one or more digits
        ' Remove `-?` if you only want positive integers
        regex.Pattern = "^-?\d+$"
        
        If Not regex.Test(txtVal) Then
            MsgBox "Only integers are allowed!"
            Cancel = True
            txtBox.SetFocus
            Exit Sub
        End If
    End If
Next j

Regex breakdown: #

• ^ = Start of the string
• -? = Optional negative sign
• \d+ = One or more numeric digits
• $ = End of the string

3. Restrict Input in Real-Time (KeyPress Event) #

For a smoother user experience, prevent invalid characters from being typed in the first place. You can either add this event to each text box or use a class module to handle all text boxes at once.

Per-TextBox KeyPress Event: #

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    ' Allow numbers, backspace, and optional negative sign (only at start)
    Select Case KeyAscii
        Case vbKeyBack ' Let users delete characters
            ' Do nothing—allow the key
        Case Asc("0") To Asc("9") ' Allow numeric digits
            ' Do nothing—allow the key
        Case Asc("-") ' Allow negative sign only if it's the first character
            If Me.ActiveControl.SelStart > 0 Then
                KeyAscii = 0 ' Cancel the key press
            End If
        Case Else
            KeyAscii = 0 ' Block all other characters (like decimals, letters)
    End Select
End Sub

Batch Handling with a Class Module: #

If you have 59 text boxes, writing individual events is tedious. Here's how to do it in bulk:

1. Insert a new class module (rename it clsIntegerTextBox)
2. Add this code to the class:

   Public WithEvents txtBox As MSForms.TextBox
   
   Private Sub txtBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
       ' Same KeyPress logic as above
       Select Case KeyAscii
           Case vbKeyBack
           Case Asc("0") To Asc("9")
           Case Asc("-")
               If txtBox.SelStart > 0 Then KeyAscii = 0
           Case Else
               KeyAscii = 0
       End Select
   End Sub
   

3. In your UserForm's Initialize event, add:

   Private textBoxes As Collection
   
   Private Sub UserForm_Initialize()
       Set textBoxes = New Collection
       Dim ctrl As Control
       Dim clsTxt As clsIntegerTextBox
       
       ' Loop through all text boxes and bind them to the class
       For Each ctrl In Me.Controls
           If TypeName(ctrl) = "TextBox" Then
               Set clsTxt = New clsIntegerTextBox
               Set clsTxt.txtBox = ctrl
               textBoxes.Add clsTxt
           End If
       Next ctrl
   End Sub
   

This way, all text boxes automatically enforce integer-only input without writing 59 separate events!

内容的提问来源于stack exchange，提问作者Eatate