如何让基于Data表的Worksheet_Change代码在所有工作表生效

AI 小北

2026-8-26

问题背景

工作簿内共30个工作表，所有工作表的C6:C10区域设置了下拉选择列表
需求为：在任意工作表的C6:C10区域选择下拉值后，自动将匹配结果填充到同表对应行的D列
匹配规则：以工作簿内名为Data的工作表为统一数据源，匹配Data表A列与下拉选中值一致的行，返回对应B列的内容
现有代码为工作表级事件代码，仅在存放源数据的工作表内可正常运行，需要改造为全工作表通用逻辑
初始疑问：是否可通过添加select.worksheet.data类的工作表选择语句实现需求

原有问题代码

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Res As Variant
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("c6:c10")) Is Nothing Then
Res = Evaluate("INDEX(b2:b63,MATCH(" & Target.Address & ",A2:a63,0))").
If Not IsError(Res) Then Target.Offset(, 1) = Res
End If
End Sub

解决方案

不需要添加select.worksheet.data类的Select语句，Select操作会触发不必要的工作表切换，运行效率低且容易引发事件递归报错。
原代码失效的核心原因有两点：一是代码为单工作表级的Worksheet_Change事件，仅在代码所在的工作表修改时才会触发；二是公式中引用的A2:A63、B2:B63范围没有指定所属工作表，默认读取当前触发事件的工作表内容，切换到其他工作表时自然无法匹配到Data表的数据源。
正确改造方式是使用工作簿级的SheetChange事件，将代码放在ThisWorkbook模块中即可对所有工作表生效，所有范围引用明确指定所属工作表对象，无需切换工作表。
操作步骤：打开VBA编辑器，在左侧工程资源管理器中双击ThisWorkbook对象，粘贴以下代码即可：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Res As Variant
    Dim dataSht As Worksheet
    
    ' 跳过数据源表本身，避免编辑Data表时触发无意义匹配
    If Sh.Name = "Data" Then Exit Sub
    Set dataSht = ThisWorkbook.Worksheets("Data")
    
    ' 仅单单元格修改时触发逻辑，避免多单元格选中操作报错
    If Target.CountLarge > 1 Then Exit Sub
    ' 判断修改位置是否在当前工作表的下拉区域C6:C10
    If Not Intersect(Target, Sh.Range("C6:C10")) Is Nothing Then
        ' 明确指定查找范围为Data表对应列，避免范围引用错误
        Res = Application.Evaluate( _
            "INDEX('Data'!B2:B63,MATCH(" & Target.Address & ",'Data'!A2:A63,0))")
        ' 匹配成功则写入D列对应单元格
        If Not IsError(Res) Then
            ' 临时关闭事件，避免写入单元格触发递归报错
            Application.EnableEvents = False
            Target.Offset(0, 1) = Res
            Application.EnableEvents = True
        End If
    End If
End Sub

关键修改说明

替换为工作簿级Workbook_SheetChange事件，所有工作表的单元格修改操作都会触发该事件，无需给30个工作表逐个粘贴代码
所有范围引用明确绑定所属对象：下拉区域用Sh.Range指代当前触发修改的工作表，查找范围加'Data'!前缀指定数据源表，彻底解决范围取错工作表的问题
增加事件开关逻辑：写入D列前临时关闭事件响应，避免写入操作再次触发Change事件导致递归栈溢出
自动排除Data工作表的修改操作，减少无意义的逻辑运行
全程通过对象引用访问Data表数据，不需要切换活动工作表，不会打断用户当前操作，运行效率更高

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