Excel VBA Worksheet_Change事件问题：多单元格关联工作表显示异常

问题说明 #

长期使用Excel但极少接触VBA的用户编写了Worksheet_Change事件代码，期望实现：当「项目相关性」列（D列）的单元格选择"Yes"时，显示对应部门工作表；否则隐藏。但当前代码存在逻辑缺陷：同一部门对应多个单元格时，必须所有单元格都选择"Yes"，工作表才会显示。需优化为只要该部门对应的任意一个单元格选择"Yes"，就自动显示工作表。

原代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    If [D24] = "Yes" Then
        Sheets("3-PSSR").Visible = True
    Else
        Sheets("3-PSSR").Visible = False
    End If
    If [D25] = "Yes" Then
        Sheets("3-PSSR").Visible = True
    Else
        Sheets("3-PSSR").Visible = False
    End If
    If [D26] = "Yes" Then
        Sheets("6-Procurement").Visible = True
    Else
        Sheets("6-Procurement").Visible = False
    End If
    If [D28] = "Yes" Then
        Sheets("5-Engineering").Visible = True
    Else
        Sheets("5-Engineering").Visible = False
    End If
    If [D29] = "Yes" Then
        Sheets("5-Engineering").Visible = True
    Else
        Sheets("5-Engineering").Visible = False
    End If
    If [D30] = "Yes" Then
        Sheets("5-Engineering").Visible = True
    Else
        Sheets("5-Engineering").Visible = False
    End If
    If [D27] = "Yes" Then
        Sheets("8-HSE").Visible = True
    Else
        Sheets("8-HSE").Visible = False
    End If
    If [D31] = "Yes" Then
        Sheets("8-HSE").Visible = True
    Else
        Sheets("8-HSE").Visible = False
    End If
    If [D32] = "Yes" Then
        Sheets("8-HSE").Visible = True
    Else
        Sheets("8-HSE").Visible = False
    End If
    If [D33] = "Yes" Then
        Sheets("2-Equipment").Visible = True
    Else
        Sheets("2-Equipment").Visible = False
    End If
    If [D34] = "Yes" Then
        Sheets("2-Equipment").Visible = True
    Else
        Sheets("2-Equipment").Visible = False
    End If
    If [D35] = "Yes" Then
        Sheets("4-Quality").Visible = True
    Else
        Sheets("4-Quality").Visible = False
    End If
    If [D36] = "Yes" Then
        Sheets("7-Organisation").Visible = True
    Else
        Sheets("7-Organisation").Visible = False
    End If
End Sub

优化方案 #

核心思路 #

先默认隐藏所有目标部门工作表，再逐个检查每个部门对应的D列单元格区域：只要区域内存在至少一个"Yes"，就将对应工作表设为可见。这样能确保只要有一个关联单元格选"Yes"，工作表就显示；只有当所有关联单元格都非"Yes"时，工作表才隐藏。

优化后代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅处理D列24-36行的单元格变更，避免不必要的触发
    If Intersect(Target, Range("D24:D36")) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False ' 关闭事件触发，防止循环执行
    
    ' 1. 先默认隐藏所有目标工作表
    Sheets("3-PSSR").Visible = False
    Sheets("6-Procurement").Visible = False
    Sheets("5-Engineering").Visible = False
    Sheets("8-HSE").Visible = False
    Sheets("2-Equipment").Visible = False
    Sheets("4-Quality").Visible = False
    Sheets("7-Organisation").Visible = False
    
    ' 2. 检查各部门对应的单元格区域，存在"Yes"则显示工作表
    If WorksheetFunction.CountIf(Range("D24:D25"), "Yes") > 0 Then
        Sheets("3-PSSR").Visible = True
    End If
    
    If WorksheetFunction.CountIf(Range("D26"), "Yes") > 0 Then
        Sheets("6-Procurement").Visible = True
    End If
    
    If WorksheetFunction.CountIf(Range("D28:D30"), "Yes") > 0 Then
        Sheets("5-Engineering").Visible = True
    End If
    
    If WorksheetFunction.CountIf(Range("D27,D31:D32"), "Yes") > 0 Then
        Sheets("8-HSE").Visible = True
    End If
    
    If WorksheetFunction.CountIf(Range("D33:D34"), "Yes") > 0 Then
        Sheets("2-Equipment").Visible = True
    End If
    
    If WorksheetFunction.CountIf(Range("D35"), "Yes") > 0 Then
        Sheets("4-Quality").Visible = True
    End If
    
    If WorksheetFunction.CountIf(Range("D36"), "Yes") > 0 Then
        Sheets("7-Organisation").Visible = True
    End If
    
    Application.EnableEvents = True ' 恢复事件触发
End Sub

代码关键点说明 #

• 范围限制：通过Intersect(Target, Range("D24:D36"))判断，仅当变更的单元格在目标区域内时才执行代码，减少不必要的性能消耗。
• 事件关闭：修改工作表可见性会触发Worksheet_Change事件，关闭Application.EnableEvents可避免循环执行。
• 批量检查：使用CountIf函数快速统计区域内"Yes"的数量，只要数量大于0就显示工作表，逻辑简洁高效。
• 可维护性：将同一部门的单元格归为一个区域，后续调整单元格范围只需修改对应的Range参数即可。

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