VBA多If Then语句使用求助：满足条件时脚本未停止运行

Hey there! Let's break down why your script wasn't stopping when it hit those validation checks, and fix it up to work exactly how you want it to.

The Core Problem With Your Original Code #

Your original code uses separate, standalone If statements. That means even if the first MsgBox pops up (say, for an empty name in C5), the script will keep running through all the other If checks, then still execute the Save and Worksheet.Select lines. There was no logic to pause or terminate the script as soon as a problem was found.

Optimized Solution (With Step-by-Step Improvements) #

Here's a revised version of your code that fixes the stopping issue, adds better user experience, and includes guardrails to avoid errors:

Sub BasicInfo()
    ' Check each required field one at a time
    If Range("C5").Value = "" Then
        MsgBox "Please enter a Name!", vbExclamation, "Missing Information"
        Range("C5").Activate ' Jump directly to the cell needing input
        Exit Sub ' Stop the script immediately
    End If
    
    If Range("C6").Value = "" Then
        MsgBox "Please enter your Designation!", vbExclamation, "Missing Information"
        Range("C6").Activate
        Exit Sub
    End If
    
    If Range("C8").Value = 1 Then
        MsgBox "Please enter your Gender!", vbExclamation, "Invalid Selection"
        Range("C8").Activate
        Exit Sub
    End If
    
    If Range("C9").Value = 1 Then
        MsgBox "Please enter Years of Experience!", vbExclamation, "Invalid Selection"
        Range("C9").Activate
        Exit Sub
    End If
    
    If Range("C10").Value = 1 Then
        MsgBox "Please enter Years of Experience in present Job!", vbExclamation, "Invalid Selection"
        Range("C10").Activate
        Exit Sub
    End If
    
    If Range("C11").Value = 1 Then
        MsgBox "Please enter your Date of Birth!", vbExclamation, "Invalid Selection"
        Range("C11").Activate
        Exit Sub
    End If
    
    If Range("D11").Value = 1 Then
        MsgBox "Please enter your Month of Birth!", vbExclamation, "Invalid Selection"
        Range("D11").Activate
        Exit Sub
    End If
    
    If Range("E11").Value = 1 Then
        MsgBox "Please enter your Year of Birth!", vbExclamation, "Invalid Selection"
        Range("E11").Activate
        Exit Sub
    End If
    
    If Range("C12").Value = 1 Then
        MsgBox "Please enter marital Status!", vbExclamation, "Invalid Selection"
        Range("C12").Activate
        Exit Sub
    End If
    
    If Range("C13").Value = 1 Then
        MsgBox "Please enter your Education!", vbExclamation, "Invalid Selection"
        Range("C13").Activate
        Exit Sub
    End If
    
    If Range("C14").Value = 1 Then
        MsgBox "Please enter your Nationality!", vbExclamation, "Invalid Selection"
        Range("C14").Activate
        Exit Sub
    End If
    
    ' Only run this if ALL checks pass
    ActiveWorkbook.Save
    
    ' Avoid errors if we're on the last worksheet
    If ActiveSheet.Index < ThisWorkbook.Worksheets.Count Then
        Worksheets(ActiveSheet.Index + 1).Select
    Else
        MsgBox "This is the last worksheet!", vbInformation, "No More Sheets"
    End If
End Sub

Key Improvements Explained #

• Exit Sub for immediate termination: As soon as a validation failure is found, this command stops the script cold—no more running through unnecessary checks or executing the save/switch logic prematurely.
• Cell activation: Range("C5").Activate sends the user directly to the cell that needs fixing, so they don't have to hunt for it.
• Clearer message boxes: Added titles ("Missing Information") and warning icons (vbExclamation) to make prompts more intuitive.
• Last sheet guardrail: Prevents an error if you're already on the final worksheet (your original code would crash here).

Bonus: A More Concise Version (For Future Maintenance) #

If you ever need to add more validation checks, this array-based version will save you from repeating code. It's cleaner and easier to update:

Sub BasicInfo_Concise()
    Dim checks As Variant
    Dim i As Integer
    
    ' Store all validation rules in an array: {Cell Address, Error Message}
    checks = Array( _
        Array("C5", "Please enter a Name!"), _
        Array("C6", "Please enter your Designation!"), _
        Array("C8", "Please enter your Gender!"), _
        Array("C9", "Please enter Years of Experience!"), _
        Array("C10", "Please enter Years of Experience in present Job!"), _
        Array("C11", "Please enter your Date of Birth!"), _
        Array("D11", "Please enter your Month of Birth!"), _
        Array("E11", "Please enter your Year of Birth!"), _
        Array("C12", "Please enter marital Status!"), _
        Array("C13", "Please enter your Education!"), _
        Array("C14", "Please enter your Nationality!") _
    )
    
    ' Loop through each validation rule
    For i = LBound(checks) To UBound(checks)
        Dim targetCell As Range
        Set targetCell = Range(checks(i)(0))
        
        ' Check for empty value OR value = 1 (adjust condition if needed)
        If targetCell.Value = "" Or targetCell.Value = 1 Then
            MsgBox checks(i)(1), vbExclamation, "Validation Error"
            targetCell.Activate
            Exit Sub
        End If
    Next i
    
    ' All checks passed
    ActiveWorkbook.Save
    If ActiveSheet.Index < ThisWorkbook.Worksheets.Count Then
        Worksheets(ActiveSheet.Index + 1).Select
    Else
        MsgBox "This is the last worksheet!", vbInformation
    End If
End Sub

Just add a new line to the checks array whenever you need to validate a new cell—no extra If statements required!

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