Excel VBA：基于Table18单元格值控制工作表显隐的代码优化求助

完整优化代码 #

将以下代码粘贴到Table18所在工作表的模块中（右键工作表标签 → 查看代码）：

' 表格数据变更时自动触发
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject
    Set tbl = Me.ListObjects("Table18")
    
    ' 仅当修改区域在表格范围内时执行更新
    If Not Intersect(Target, tbl.DataBodyRange) Is Nothing Then
        Call UpdateSheetVisibility(tbl)
    End If
End Sub

' 核心逻辑：根据表格数据更新工作表显隐状态
Sub UpdateSheetVisibility(tbl As ListObject)
    Dim ws As Worksheet
    Dim row As ListRow
    ' 定义映射关系：表格值 → 对应工作表名称
    Dim groupMap As Object, typeMap As Object
    
    ' 初始化映射字典
    Set groupMap = CreateObject("Scripting.Dictionary")
    groupMap(1) = "Group 1"
    groupMap(2) = "Group 2"
    groupMap(3) = "Group 3"
    groupMap(3) = "Group 4" ' 对应原逻辑中Group=3需显示的两个工作表
    
    Set typeMap = CreateObject("Scripting.Dictionary")
    typeMap("Business Division") = "Business Division Sheet"
    typeMap("Complexity") = "Complexity Sheet"
    typeMap("Location") = "Location Sheet"
    
    ' 先隐藏所有目标工作表
    For Each key In groupMap.Keys
        On Error Resume Next ' 避免工作表不存在导致报错
        Set ws = ThisWorkbook.Sheets(groupMap(key))
        If Not ws Is Nothing Then ws.Visible = xlSheetHidden
        Set ws = Nothing
        On Error GoTo 0
    Next
    For Each key In typeMap.Keys
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(typeMap(key))
        If Not ws Is Nothing Then ws.Visible = xlSheetHidden
        Set ws = Nothing
        On Error GoTo 0
    Next
    
    ' 遍历表格所有行，匹配值则显示对应工作表
    If Not tbl.DataBodyRange Is Nothing Then
        For Each row In tbl.ListRows
            ' 处理Group列（第2列）
            If groupMap.Exists(row.Range(2).Value) Then
                On Error Resume Next
                ThisWorkbook.Sheets(groupMap(row.Range(2).Value)).Visible = xlSheetVisible
                On Error GoTo 0
            End If
            ' 处理Custom Type列（第4列）
            If typeMap.Exists(row.Range(4).Value) Then
                On Error Resume Next
                ThisWorkbook.Sheets(typeMap(row.Range(4).Value)).Visible = xlSheetVisible
                On Error GoTo 0
            End If
        Next row
    End If
End Sub

优化点说明 #

1. 实时响应多行数据变更

   • 通过Worksheet_Change事件监听表格区域修改，数据变更自动触发更新
   • 先默认隐藏所有目标工作表，再遍历表格每一行，只要有一行匹配就显示对应工作表，彻底解决原代码仅识别最后一行的问题

2. 使用工作表名称引用

   • 用字典建立表格值与工作表名称的映射（如groupMap(1) = "Group 1"），直接通过ThisWorkbook.Sheets("工作表名称")引用，比Sheet编号更直观且不易因工作表顺序变动失效

3. 合并范围处理

   • 直接遍历表格的ListRows，每一行同时读取第2列（Group）和第4列（Custom Type）的值，无需拆分两个独立范围

4. 代码精简优化

   • 用字典管理映射关系，避免重复编写多轮循环判断
   • 统一错误处理，防止因工作表名称错误导致代码崩溃
   • 逻辑拆分：事件监听与核心处理分离，代码结构更清晰

注意：请根据实际工作表名称修改字典中groupMap和typeMap的对应值，确保与你的工作表名称完全一致

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