Excel多文档打开时保持Workbook隐藏仅显示UserForm1的问题

步骤1：创建类模块捕获应用程序级事件 #

1. 按Alt+F11打开VBA编辑器，右键点击当前项目 → 插入 → 类模块。
2. 在属性窗口的(名称)栏，把类模块名称改成AppEvents。
3. 在这个类模块里粘贴以下代码：

Private WithEvents app As Excel.Application

Private Sub Class_Initialize()
    Set app = Excel.Application
End Sub

' 监听其他工作簿打开事件
Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    If Not Wb Is ThisWorkbook Then
        HideThisWorkbookWindow
    End If
End Sub

' 监听其他工作簿关闭事件
Private Sub app_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    If Not Wb Is ThisWorkbook Then
        HideThisWorkbookWindow
    End If
End Sub

' 封装隐藏当前工作簿窗口的逻辑
Private Sub HideThisWorkbookWindow()
    Dim hasVisibleWB As Boolean
    Dim wb As Workbook
    Dim win As Window
    
    hasVisibleWB = False
    For Each wb In Application.Workbooks
        If Not wb Is ThisWorkbook Then
            For Each win In wb.Windows
                If win.Visible Then
                    hasVisibleWB = True
                    Exit For
                End If
            Next win
            If hasVisibleWB Then Exit For
        End If
    Next wb
    
    If hasVisibleWB Then
        ThisWorkbook.Windows(1).Visible = False
    Else
        Application.Visible = False
    End If
End Sub

步骤2：初始化事件监听 #

修改你原来的标准模块代码，添加全局变量保存类实例，确保事件监听持续生效：

' 全局变量，防止类实例被垃圾回收销毁
Private appEvents As AppEvents

Sub showLoginForm()
    ' 初始化事件监听
    Set appEvents = New AppEvents
    
    If isSheetVisible Then
        ThisWorkbook.Windows(1).Visible = False
    Else
        Application.Visible = False
    End If
    UserForm1.Show vbModeless
End Sub

Function isSheetVisible() As Boolean
    Dim wb As Workbook
    Dim win As Window
    
    For Each wb In Application.Workbooks
        If Not wb Is ThisWorkbook Then
            For Each win In wb.Windows
                If win.Visible Then
                    isSheetVisible = True
                    Exit Function
                End If
            Next
        End If
    Next
End Function

步骤3：添加工作簿关闭清理逻辑 #

在工作簿代码模块里补充关闭事件，避免内存泄漏和Excel进程残留：

Private Sub Workbook_Open()
    UserForm1_Initialize ' 保留你原有代码
    showLoginForm
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 释放类实例
    Set appEvents = Nothing
    ' 恢复Excel可见性，防止关闭后进程残留
    Application.Visible = True
End Sub

原理说明 #

• 通过类模块的WithEvents绑定Excel应用程序，能监听所有工作簿的打开/关闭动作。
• 每当其他工作簿打开或关闭时，自动触发隐藏逻辑，重新检查并设置当前工作簿窗口为隐藏状态。
• 全局变量appEvents必须声明为模块级，否则类实例会被自动销毁，导致事件监听失效。

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