Excel VBA：设置E23为手动输入/自动引用B23的问题

我来帮你搞定这个问题！你的需求是让E23支持手动输入值，空值时自动同步B23的内容，原代码在手动修改E23时能正常工作，但通过下拉框修改B23时出现异常，核心问题在于原代码的触发逻辑覆盖不全，而且没有处理事件循环的潜在风险。

原代码的问题分析 #

你的现有代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$23" And Target.Value = "" Then
        Target.Formula = "=B23"
    End If
End Sub

存在两个关键局限：

• 仅在用户主动清空E23时才设置引用B23的公式，如果用户之前手动输入过E23的值，后续修改B23时，E23不会自动切换回B23的内容。
• 当E23是公式状态时，修改B23会触发公式计算，但不会触发Worksheet_Change事件（该事件仅响应单元格的直接修改，而非公式计算结果的变化），如果下拉框修改B23属于这类场景，就会出现同步失效的异常。
• 设置公式时会再次触发Worksheet_Change，虽然当前条件不会导致死循环，但存在潜在的事件冲突风险。

改进后的解决方案 #

下面的代码会全面覆盖所有场景：用户手动修改E23、用户修改B23（包括下拉框方式），同时避免事件循环，确保E23始终遵循“有输入用输入，无输入同步B23”的规则。

在对应的工作表模块中替换为以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 当用户直接修改E23或B23时触发更新
    If Target.Address = "$E$23" Or Target.Address = "$B$23" Then
        UpdateE23Value
    End If
End Sub

Private Sub Worksheet_Calculate()
    ' 处理B23通过下拉框、公式计算等间接方式变化的场景（这类操作不会触发Change事件）
    UpdateE23Value
End Sub

Private Sub UpdateE23Value()
    ' 关闭事件触发，避免循环执行
    Application.EnableEvents = False
    ' 确保出错时能恢复事件状态
    On Error GoTo EventCleanup

    ' 核心逻辑：E23为空则同步B23的值，否则保留用户输入
    If Range("E23").Value = "" Then
        Range("E23").Value = Range("B23").Value
    End If

EventCleanup:
    ' 恢复事件触发状态
    Application.EnableEvents = True
    ' 清除错误状态
    Err.Clear
End Sub

代码说明 #

• UpdateE23Value是核心子过程，负责实现E23的同步逻辑，同时通过Application.EnableEvents = False避免代码执行时反复触发事件。
• Worksheet_Change处理用户直接修改E23或B23的场景，确保立即同步。
• Worksheet_Calculate覆盖B23通过下拉框、公式计算等间接修改的场景，这类操作不会触发Change事件，但会触发Calculate事件，保证同步不遗漏。
• 使用错误处理分支EventCleanup，确保无论代码是否出错，事件触发状态都会被恢复，避免影响后续的Excel操作。

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