跨宏调用场景下Worksheet_Change事件二次校验失效问题求助

Hey there! Let's fix that broken second macro logic for you. The most common culprit here is timing—your Worksheet_Change event might be running before Excel finishes calculating the VLOOKUP in column F, or you're missing safeguards to handle event recursion and multi-cell edits.

Here's a revised, robust version of your code with explanations of the key fixes:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim dCell As Range, eCell As Range, fCell As Range
    
    ' Disable events to stop recursive triggers (critical!)
    Application.EnableEvents = False
    ' Ensure events get re-enabled even if an error occurs
    On Error GoTo Cleanup

    ' --- First Logic: Enforce D column entry before E ---
    For Each cell In Target
        If cell.Column = 5 Then ' Check if edited cell is in column E
            Set dCell = Me.Cells(cell.Row, 4) ' Grab corresponding D cell
            If IsEmpty(dCell.Value) Then
                MsgBox "Please fill in column D first before entering a value in column E!", vbExclamation
                cell.ClearContents ' Optional: Clear invalid E entry to enforce order
                GoTo Cleanup ' Exit after warning to avoid unnecessary checks
            End If
        End If
    Next cell

    ' --- Second Logic: Validate F vs E after D/F updates ---
    ' Only run checks if the edited range touches columns D, E, or F
    If Not Intersect(Target, Me.Range("D:F")) Is Nothing Then
        For Each cell In Intersect(Target, Me.Range("D:F")).EntireRow
            Set dCell = Me.Cells(cell.Row, 4)
            Set eCell = Me.Cells(cell.Row, 5)
            Set fCell = Me.Cells(cell.Row, 6)

            ' Force Excel to finish calculating the VLOOKUP first
            ' This fixes the "F hasn't updated yet" issue
            Application.Calculate

            ' Only validate if all relevant cells have values
            If Not IsEmpty(dCell.Value) And Not IsEmpty(eCell.Value) And Not IsEmpty(fCell.Value) Then
                ' Use Round() to avoid floating-point precision false mismatches
                If Round(fCell.Value, 2) <> Round(eCell.Value, 2) Then
                    MsgBox "Mismatch detected! Column F value does not match column E in row " & cell.Row & ".", vbCritical
                    ' Optional: Highlight mismatched cells for clarity
                    eCell.Interior.Color = vbYellow
                    fCell.Interior.Color = vbYellow
                Else
                    ' Clear highlight if values match
                    eCell.Interior.ColorIndex = xlColorIndexNone
                    fCell.Interior.ColorIndex = xlColorIndexNone
                End If
            End If
        Next cell
    End If

Cleanup:
    ' Re-enable events no matter what happens
    Application.EnableEvents = True
    On Error GoTo 0 ' Reset error handling
End Sub

Key Fixes & Explanations: #

1. Event Recursion Protection
   Application.EnableEvents = False prevents the macro from triggering itself when it modifies cells (like clearing E or highlighting mismatches). The Cleanup block ensures events always get re-enabled, even if an error occurs.

2. Wait for Calculation
   Application.Calculate forces Excel to finish computing the VLOOKUP in column F before checking values. This is almost certainly why your original second macro failed—it was checking F before the formula updated.

3. Multi-Cell Edit Handling
   The For Each cell In Target loops ensure the code works even if the user pastes or edits multiple cells at once, not just single cells.

4. Precision Safeguard
   Using Round() avoids false mismatches from Excel's floating-point precision quirks (e.g., 1.0000000001 vs 1 being treated as unequal). Adjust the decimal places (the 2 in the code) to match your data needs.

5. Target Range Limiting
   The Intersect check ensures the validation only runs when changes happen in columns D, E, or F, making the macro more efficient.

Quick Notes: #

• Make sure your worksheet is set to Automatic Calculation (go to the Formulas tab → Calculation Options → select Automatic). If it's set to manual, the VLOOKUP won't update unless you trigger a calculation manually.
• The optional cell clearing/highlighting can be removed or adjusted to fit your workflow.

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