如何在不激活工作表时显示/隐藏工作表，避免触发事件代码

核心思路 #

要规避Worksheet_Activate或Worksheet_Change事件触发，核心要做到两点：

• 操作工作表可见性前，关闭Excel的事件触发机制（Application.EnableEvents = False）
• 全程避免使用Select/Activate方法激活工作表，这类操作本身就会触发Worksheet_Activate事件

优化后的VBA代码 #

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 先关闭事件、屏幕刷新，避免触发不必要操作
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Sheets("User Database").Visible = xlVeryHidden
    
    Dim varSheet                    As Variant
    Dim arrVisibleSheets            As Variant
    Dim arrHiddenSheets             As Variant

    arrVisibleSheets = Array(Sheet5)
    arrHiddenSheets = Array(Sheet1, Sheet13, Sheet14, Sheet15, Sheet16, Sheet17, Sheet18, Sheet3, Sheet9)
    
    ' 设置可见工作表
    For Each varSheet In arrVisibleSheets
        varSheet.Visible = xlSheetVisible
    Next varSheet

    ' 设置隐藏工作表
    For Each varSheet In arrHiddenSheets
        varSheet.Visible = xlSheetHidden
    Next varSheet
    
    ' 恢复Excel设置
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Private Sub Workbook_Open()
    ' 先关闭事件、自动计算等，提升效率并避免触发事件
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .EnableEvents = False
    End With

    Dim varSheet                    As Variant
    Dim arrVisibleSheets            As Variant
    Dim arrHiddenSheets             As Variant

    arrVisibleSheets = Array(Sheet5)
    arrHiddenSheets = Array(Sheet1, Sheet13, Sheet14, Sheet15, Sheet16, Sheet17, Sheet18, Sheet3, Sheet9)

    ' 设置可见工作表（无需激活）
    For Each varSheet In arrVisibleSheets
        varSheet.Visible = xlSheetVisible
    Next varSheet

    ' 设置隐藏工作表（无需激活）
    For Each varSheet In arrHiddenSheets
        If varSheet.Visible = xlSheetVisible Then
            varSheet.Visible = xlSheetHidden
        End If
    Next varSheet
    
    ' 隐藏工作簿或Excel程序
    If Application.Windows.Count > 1 Then
        Application.Windows(ThisWorkbook.Name).Visible = False
    Else
        Application.Visible = False
    End If
    
    Sheets("User Database").Visible = xlVeryHidden
    
    ' 恢复Excel设置
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .EnableEvents = True
    End With

    A_Login_Form.Show
End Sub

关键修改说明 #

1. 调整设置顺序：所有工作表操作前就关闭EnableEvents，确保后续可见性修改不会触发任何工作表事件
2. 移除不必要的Select操作：原代码中的Sheet5.Select会激活工作表触发事件，直接移除即可，设置可见性无需激活工作表
3. 规范判断逻辑：隐藏工作表时用xlSheetVisible替代True，符合VBA官方常量规范
4. 统一设置管理：在Workbook_BeforeClose中也加入关闭事件和屏幕刷新的操作，避免关闭时触发不必要事件

额外建议 #

如果部分工作表的Worksheet_Change事件需要保留手动操作触发，可以在事件代码中加入判断：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅当EnableEvents为True（手动操作）时执行事件代码
    If Application.EnableEvents Then
        ' 原事件代码内容
    End If
End Sub

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