vba - Excel VBA 工作表事件在隐藏/取消隐藏之间切换

Question

我正在开展一个项目，以尽量减少在任何给定时间在 Excel 工作簿中可见的工作表数量。我正在尝试创建一个父工作表(用作切换按钮)来显示/隐藏子工作表。例如，我在工作簿中有 6 张工作表:输入、输入 1 和输入 2、输出、输出 1 和输出 2。输入和输出将在激活时切换隐藏和取消隐藏其他工作表。我创建了 2 个工作表级别的子函数来尝试执行此操作。第一个效果很好，但另一个只有在第一个被激活并再次隐藏时才有效。任何关于更好方法的建议都会很棒。我不确定为什么excel还没有这个功能。我尝试使用数组，但这似乎不起作用。我认为您需要单独取消隐藏每个选项卡。

'1. Inputs:

Private Sub Worksheet_Activate()

On Error Resume Next
Sheets("Input 1").Visible = True = Not Sheets("Input 1").Visible = True
Sheets("Input 2").Visible = True = Not Sheets("Input 2").Visible = True
Sheets("Input 1").Activate 'needed to deactivate inputs sheet

End Sub

'2. Outputs

Private Sub Worksheet_Activate()

On Error Resume Next
Sheets("Output 1").Visible = True = Not Sheets("Output 1").Visible = True
Sheets("Output 2").Visible = True = Not Sheets("Output 2").Visible = True
Sheets("Output 1").Activate 'needed to deactivate Outputs sheet

End Sub

Answer 1

这是您可以从 onedrive 下载的文件的链接:

https://1drv.ms/x/s!Ah_zTnaUo4DzjhWzQ3OTq9tq1APC

我没有硬编码选择每个工作表时应该发生的事情，而是在“控件”工作表上使用 ListObject(即 Excel 表格)来存储“父”工作表与其各种“子”工作表之间的关系。代码只是检查这个 ListObject 以查看哪些子项属于哪个父项，然后采取相应的措施。这有一个额外的好处，就是让那些根本不了解 VBA 的人可以很容易地添加或修改那些需要的父/子表关系。

我还实现了一个“开发者”模式，其中不会发生工作表隐藏。没有什么比尝试在将您视为单纯的“用户”的应用程序上进行开发更令人沮丧的了 :-) 您可以使用键盘快捷键 Ctrl + Shift + D 在“用户”和“开发者”模式之间切换(D 表示开发者)。

这是我刚刚放在一起的示例文件中的样子。我已将下面显示的 ListObject 添加到名为“Controls”的新工作表中，并为 ListObject 命名为“VisibleSheets”:


我还添加了一个名为 DeveloperMode 的命名范围，其值为 TRUE:


这是在“用户”模式和“开发者”模式之间切换应用程序的代码，它位于标准代码模块中:

Public Sub ToggleDeveloperMode()
    Dim ws As Worksheet

    If ActiveWorkbook.Names("DeveloperMode").Value = "=TRUE" Then
        ActiveWorkbook.Names("DeveloperMode").Value = "=FALSE"
    Else
        ActiveWorkbook.Names("DeveloperMode").Value = "=TRUE"
        For Each ws In ActiveWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next ws
    End If

End Sub

这是实际执行所有隐藏和取消隐藏的代码，也包含在标准代码模块中:

Sub DisplaySheets()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim lc As ListColumn
    Dim vMatch As Variant

    Set lo = Range("VisibleSheets").ListObject

    If Not [DeveloperMode] Then
        For Each lc In lo.ListColumns
            If lc.Name = ActiveSheet.Name Then
                For Each ws In ActiveWorkbook.Worksheets
                    Set vMatch = Nothing 'Reset from last pass
                    vMatch = Application.Match(ws.Name, lo.HeaderRowRange, 0)
                    If IsError(vMatch) Then 'It's not one of our main sheets
                        Set vMatch = Nothing 'Reset from last pass
                        vMatch = Application.Match(ws.Name, lc.Range, 0)
                        If IsError(vMatch) Then
                            ws.Visible = xlSheetVeryHidden
                        Else
                            ws.Visible = xlSheetVisible
                        End If
                    End If
                Next ws
            End If
        Next lc
    End If
End Sub

这是 ThisWorkbook 模块中的一段代码，它将 Ctrl + Shift + D 的键盘快捷键分配给 ToggleDeveloperMode 例程，以便您可以轻松地在模式之间切换。 (不要告诉用户这个键盘快捷键是什么):

Private Sub Workbook_Open()
Application.OnKey "^+D", "ToggleDeveloperMode"
End Sub

最后，这里是触发 DisplaySheets 例程的代码，它也在 ThisWorkbook 模块中:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    DisplaySheets
End Sub

这是一种享受。当我依次选择 3 个父工作表中的每一个时，我看到的内容如下:






...当我使用 Ctrl + Shift + D 快捷方式将应用程序置于“开发人员”模式时会发生这种情况，所有工作表都未隐藏，包括带有控件的工作表。


我建议为父选项卡提供与此处相同的颜色，以便用户更容易理解，无论其他选项卡选择性地出现/消失，它们都不会改变。

如果用户(或您)可能想要重命名工作表，请使用代号而不是工作表名。如果您不确定我在说什么，请告诉我。