VBA表单错误检查If语句失效求助（0-1正常，2-15异常）

Let's break down why your current code isn't working for values 2-15, and fix it with cleaner, more reliable logic.

The Root of the Problem #

Your existing code uses ElseIf for multi-row checks, which creates a mutually exclusive logic flow that breaks the error detection:

• For SRU=3, if SRUName1 is empty, the code stops there and never checks SRUName2 or SRUName3.
• If SRUName1 is valid but SRUName2 is empty, the code sets Sig_2 to an error but never updates Sig_1 to its normal state (leaving old error text or uninitialized values).
• Only when all checked fields are valid does it set all Sig_X values to normal—this means any single empty field leaves the other Sig entries in an incorrect state, making it look like all rows are invalid.

The Fixed Code #

We'll rewrite the logic to use loops (to avoid redundant code for 15 rows) and independent checks for each visible row:

Sub Check_SRU_Table()
    Dim selectedSRU As Integer
    Dim i As Integer
    
    ' Get the selected SRU value, convert to integer
    On Error Resume Next ' Handle non-numeric values gracefully
    selectedSRU = CInt(Range("SRUAdd").Value)
    On Error GoTo 0
    
    ' Clamp value to valid range (0-15)
    If selectedSRU < 0 Then selectedSRU = 0
    If selectedSRU > 15 Then selectedSRU = 15
    
    ' Reset all signature fields first
    For i = 1 To 15
        If i <= selectedSRU Then
            ' Initialize visible rows to normal state
            Range("Sig_" & i).Value = "Signature Release " & i
        Else
            ' Optional: Clear state for hidden rows (adjust as needed)
            Range("Sig_" & i).Value = ""
        End If
    Next i
    
    ' Special case for SRU=0 (matches your original behavior)
    If selectedSRU = 0 Then
        For i = 1 To 15
            Range("Sig_" & i).Value = "Signature Release " & i
        Next i
        Exit Sub
    End If
    
    ' Check each visible row for empty values
    For i = 1 To selectedSRU
        If Range("SRUName" & i).Value = "" Then
            Range("Sig_" & i).Value = "Signature Release " & i & " - ERROR"
        End If
    Next i
End Sub

Private Sub BtnCheck_Click()
    Check_SRU_Table
End Sub

Key Improvements #

1. Loop-Based Logic: No more repeating code for each SRU value—this scales easily to 15 rows and is far easier to maintain.
2. Initialization First: We set all visible Sig fields to their normal state before checking for errors. This ensures no old error messages linger if a field is fixed.
3. Independent Error Checks: Each row is checked separately, so multiple empty fields will all show their respective error messages (instead of only the first one).
4. Input Validation: We convert and clamp the selected SRU value to avoid invalid ranges (0-15) that could break the code.

Why Your Original SRU=3 Case Failed #

In your original code, if SRUName1 was valid but SRUName2 was empty, the code would set Sig_2 to an error but never update Sig_1 to its normal text. If Sig_1 had an old error message from a previous check, it would stay visible, making it look like all rows were invalid. The fixed code ensures every visible row starts with the correct normal state before error checking.

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