公式触发单元格变更场景下Worksheet_Change的替代方案问询

你说得对，Worksheet_Change仅响应手动单元格变更，公式计算导致的单元格值变化需要用Worksheet_Calculate事件来捕获。下面是针对你的需求优化后的实现方案：

需求匹配逻辑 #

• 当Brad1a~Brad7a/Nick1a~Nick7a（公式驱动单元格）变为"YES"时，对应Brad1b~Brad7b/Nick1b~Nick7b设为"Enter %"；变为"no"时强制设为0
• 手动修改Brad#b/Nick#b的值后，需保留该值，直到对应#a单元格变为"no"才重置

优化后代码 #

Private Sub Worksheet_Calculate()
    Dim ws As Worksheet
    Dim i As Integer
    Dim namePrefixes As Variant
    
    ' 绑定目标工作表
    Set ws = ThisWorkbook.Sheets("Resource Allocation")
    ' 定义需要批量处理的前缀
    namePrefixes = Array("Brad", "Nick")
    
    ' 禁用事件，避免计算循环触发
    Application.EnableEvents = False
    
    ' 遍历所有目标单元格对
    For Each prefix In namePrefixes
        For i = 1 To 7
            Dim cellA As Range, cellB As Range
            Set cellA = Me.Range(prefix & i & "a")
            Set cellB = ws.Range(prefix & i & "b")
            
            Select Case UCase(cellA.Value)
                Case "YES"
                    ' 仅当b单元格处于初始状态时更新，避免覆盖手动输入值
                    If cellB.Value = "0" Or cellB.Value = "Enter %" Then
                        cellB.Value = "Enter %"
                    End If
                Case "NO"
                    ' 无论之前值是什么，强制重置为0
                    cellB.Value = "0"
            End Select
        Next i
    Next prefix
    
    ' 恢复事件触发
    Application.EnableEvents = True
End Sub

关键注意事项 #

• 代码需放在公式所在的工作表模块中（右键工作表标签→查看代码），而非标准模块
• 确保名称管理器中Brad1a~Brad7a、Nick1a~Nick7a、Brad1b~Brad7b、Nick1b~Nick7b的单元格引用完全正确
• Worksheet_Calculate会在工作表每次计算时触发，代码已做精简处理，避免影响性能

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