单元格变更触发宏问题：以下代码无法运行，请指导

Hey there, let's break down why your current code isn't behaving as expected and fix it step by step.

The Core Problem #

Your code is overwriting the target parameter (which represents the cell that was just changed) by setting Set target = Range("D11") right at the start. This means no matter which cell the user edits, your macro will always check the value of D11—instead of reacting to the cell that was actually modified. Worse, if someone edits a cell other than D11, your code will still run those If checks against D11's value, which isn't what you want.

Corrected Code #

Here's the revised version that works as intended:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only run this code if the changed cell is D11
    If Not Intersect(Target, Me.Range("D11")) Is Nothing Then
        ' Disable events to prevent infinite loops (in case your macros modify cells)
        Application.EnableEvents = False
        
        On Error GoTo Cleanup ' Ensure we re-enable events even if an error occurs
        
        Select Case UCase(Target.Value)
            Case "YES"
                Call main
            Case "NO"
                Call Main2
        End Select
        
Cleanup:
        ' Re-enable events no matter what
        Application.EnableEvents = True
    End If
End Sub

Key Improvements Explained #

• Intersect Check: We use Intersect(Target, Me.Range("D11")) to confirm that the cell being edited is exactly D11. If it's any other cell, the macro does nothing.
• Disable Events: Application.EnableEvents = False stops the Worksheet_Change event from firing again if your main or Main2 macros modify other cells in the worksheet (this prevents infinite loops).
• Error Handling: The On Error GoTo Cleanup ensures that even if something goes wrong in your main/Main2 macros, we always re-enable events—otherwise, future worksheet changes won't trigger macros at all.
• UCase Comparison: Using UCase(Target.Value) makes the check case-insensitive (so "yes", "YES", or "Yes" all trigger the same action).

Quick Notes #

• Make sure your main and Main2 macros are in the same workbook (either in the same worksheet module or a standard module).
• Test by editing cell D11 to "Yes" or "No"—the corresponding macro should run immediately.

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