指定条件下保持Excel单元格内容不变的VBA代码问题解决

Let's break down why your current code isn't working as expected, then fix it step by step:

Issues with the original code: #

• Unrestricted event triggers: Right now, any cell change on the worksheet will run this code, which is inefficient and can cause unexpected behavior (like overwriting AK14 even when you don't mean to).
• Event loop risk: When you set AK14.Value = AL8.Value, this triggers the Worksheet_Change event again, creating an infinite loop that can freeze Excel.
• No handling for manual edits: The code doesn't account for when users try to edit AK14 while AL2 is 1 (it'll just overwrite the edit immediately) or properly respect manual edits when AL2 isn't 1.

Corrected VBA Code #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Disable events to prevent infinite loops when we modify AK14
    Application.EnableEvents = False
    
    On Error GoTo Cleanup ' Ensure events get re-enabled even if an error occurs
    
    ' Check if the changed range includes AL2 or AL8 (the cells that affect our logic)
    If Not Intersect(Target, Me.Range("AL2, AL8")) Is Nothing Then
        If Me.Range("AL2").Value = 1 Then
            ' Sync AK14 to AL8's value when AL2 is 1
            Me.Range("AK14").Value = Me.Range("AL8").Value
        End If
    ' Allow manual edits to AK14 only when AL2 is NOT 1
    ElseIf Not Intersect(Target, Me.Range("AK14")) Is Nothing Then
        If Me.Range("AL2").Value = 1 Then
            ' Revert AK14 to AL8's value if user tries to edit it while AL2 is 1
            Me.Range("AK14").Value = Me.Range("AL8").Value
        End If
    End If

Cleanup:
    ' Re-enable events no matter what
    Application.EnableEvents = True
End Sub

What this code does: #

• Prevents loops: We toggle Application.EnableEvents off before modifying AK14, so the change doesn't trigger the event again.
• Targeted triggers: Only runs logic when AL2, AL8, or AK14 is modified (no unnecessary runs from other cell changes).
• Respects your requirements:
  • When AL2 is 1: AK14 automatically syncs to AL8 whenever AL2 or AL8 changes, and blocks manual edits to AK14 by reverting any changes.
  • When AL2 is not 1: Users can edit AK14 freely, and the code won't interfere with their edits.

Notes: #

• Use Me instead of ActiveSheet to make the code more reliable (it always refers to the worksheet the code is attached to, not whatever sheet is currently active).
• The On Error GoTo Cleanup ensures events are always re-enabled even if an error occurs (critical to avoid breaking future event triggers).

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