Worksheet_Change宏列动作异常：无法稳定触发对应列操作

Let's break down why your macro isn't working reliably and fix it step by step. The core issues are misordered logic with GoTo statements, unmanaged event disabling, and redundant code that's causing some actions to never trigger.

The Root Problems #

1. GoTo skips critical logic: When you edit column L (Column=12), the first If Target.Column =12 triggers AddWorkStatusDate, which exits the sub immediately. This means the checks for "Fees Received" or "Policy No. Issued" never run.
2. EnableEvents is mishandled: You're setting it multiple times, and if an error occurs, it might never get re-enabled—breaking all future worksheet change events.
3. Debug MsgBoxes: Those popups interrupt normal workflow and can cause unexpected behavior.
4. Duplicate code: The FeesReceived and PolicyNoIssued blocks are identical, leading to unnecessary repetition.

Fixed Macro Code #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim answer As Integer
    Dim LRowCompleted As Long ' Use Long instead of Integer to avoid row limit issues
    Dim rng As Range
    
    ' Disable events to prevent infinite loops, only do this once at the start
    Application.EnableEvents = False
    ' Ensure we re-enable events even if an error occurs
    On Error GoTo Cleanup

    ' Handle Column A: Auto-fill date in Column B when A is edited
    If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
        For Each rng In Intersect(Target, Me.Range("A:A"))
            If Not VBA.IsEmpty(rng.Value) Then
                rng.Offset(0, 1).Value = Now
                rng.Offset(0, 1).NumberFormat = "dd/mm/yyyy"
                ' Clear fill color from the cell 3 rows below without using Select
                rng.Offset(3, 1).Interior.Pattern = xlNone
            Else
                rng.Offset(0, 1).ClearContents
            End If
        Next rng
    End If

    ' Handle Column L: First check for copy-to-Income values, then fill date in Column M
    If Not Intersect(Target, Me.Range("L:L")) Is Nothing Then
        For Each rng In Intersect(Target, Me.Range("L:L"))
            ' Check if we need to copy to Income sheet
            If rng.Value = "Fees Received" Or rng.Value = "Policy No. Issued" Then
                answer = MsgBox("Do you want to copy this client to the Income Worksheet?", vbQuestion + vbYesNo)
                If answer = vbYes Then
                    ' Avoid using Select/Activate for faster, more reliable code
                    Me.Range("A" & rng.Row).Copy
                    Sheets("Income").Range("A" & Sheets("Income").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValues
                Else
                    MsgBox "This client will not be copied to the Income Worksheet"
                End If
            End If

            ' Always fill date in Column M (whether we copied or not)
            If Not VBA.IsEmpty(rng.Value) Then
                rng.Offset(0, 1).Value = Now
                rng.Offset(0, 1).NumberFormat = "dd/mm/yyyy"
            Else
                rng.Offset(0, 1).ClearContents
            End If
        Next rng
    End If

Cleanup:
    ' Re-enable events no matter what
    Application.EnableEvents = True
    ' Clear the copy clipboard to remove the "marching ants" selection
    Application.CutCopyMode = False
End Sub

Key Improvements Explained #

• Structured logic instead of GoTo: We group actions by column, and within Column L, we first handle the copy-to-Income check before filling the date. This ensures all intended actions run.
• Safe event management: We only disable events once, and use an error handler to guarantee they're re-enabled—even if the macro hits an error.
• No more Select/Activate: Directly referencing cells makes the macro faster and less prone to bugs (especially if users switch sheets mid-execution).
• Consolidated duplicate code: The copy logic for both "Fees Received" and "Policy No. Issued" is merged into one block.
• Long instead of Integer: Excel supports more rows than Integer can handle (32767 max), so Long prevents overflow errors.
• Removed debug popups: The MsgBox lines for target column/value are removed to avoid interrupting your workflow.

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