excel - 选择“主”工作表时自动隐藏/取消隐藏“子”工作表

问题描述

问题

我正在运行一个宏，它会根据用户输入创建几个新的工作表；至少一个“主”工作表和几个“子”工作表。这些将具有“Group 1 Master”、“Group 1 Sub 1”、“Group 2 Sub 2”、“Group 2 Sub 3”......“Group n Master”、“Group n 子 1"......

我想找到一种方法来隐藏子工作表，直到它们的主工作表被选择/激活，此时它们应该变得可见，然后当没有选择主工作表时，它们应该再次被隐藏。

方法

我找到了指示如何手动实现这一点的来源，方法是在给定的工作表中插入类似下面的子内容，但是我的工作表是由宏生成的，我不想手动完成并将其添加到所有他们。

Private Sub Worksheet_Activate()
    For Each SubSheet in SubSheets
        SubSheet.Visible = False
    Next SubSheet
End Sub

问题

除了选择它们的主工作表之外，如何隐藏所有子工作表？

谢谢

标签： excelvbaworksheet

想象一下，这是所有工作表可见时的样子：

然后这是隐藏所有子表的外观：

这就是它与自动隐藏/显示活动主工作表的子工作表的工作方式：

我使用了以下代码：

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    'find master left of selected sheet
    Dim ActiveMaster As Object
    Dim iSht As Long
    For iSht = Sh.Index To 1 Step -1    'loop backwards until a master is found
        If InStr(1, ThisWorkbook.Sheets(iSht).Name, "Master") > 0 Then
            Set ActiveMaster = ThisWorkbook.Sheets(iSht)
            Exit For
        End If
    Next iSht

    If ActiveMaster Is Nothing Then
        'no master sheet left of current sheet found
        MsgBox "No 'Master' sheet found.", vbCritical
        Exit Sub
    End If

    'find last sub sheet
    Dim LastSub As Object
    For iSht = ActiveMaster.Index + 1 To ThisWorkbook.Sheets.Count
        If InStr(1, ThisWorkbook.Sheets(iSht).Name, "Master") > 0 Then
            Set LastSub = ThisWorkbook.Sheets(iSht - 1)
            Exit For
        End If
    Next iSht

    'if no last sub sheet was found it is the very last sheet
    If LastSub Is Nothing Then
        Set LastSub = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    End If

    'hide all SUB-sheets left of master sheet and right of LastSub sheet
    If ActiveMaster.Index > 1 Then
        For iSht = 1 To ThisWorkbook.Sheets.Count
            If iSht < ActiveMaster.Index Or iSht > LastSub.Index Then
                If InStr(1, ThisWorkbook.Sheets(iSht).Name, "Master") = 0 Then
                    ThisWorkbook.Sheets(iSht).Visible = xlSheetHidden
                End If
            Else
                ThisWorkbook.Sheets(iSht).Visible = xlSheetVisible
            End If
        Next iSht
    End If
End Sub


Public Sub ShowAllSheets()
    Dim iSht As Long
    For iSht = 1 To ThisWorkbook.Sheets.Count
        ThisWorkbook.Sheets(iSht).Visible = xlSheetVisible
    Next iSht
End Sub


Public Sub HideAllSubSheets()
    Dim iSht As Long
    For iSht = 1 To ThisWorkbook.Sheets.Count
        If InStr(1, ThisWorkbook.Sheets(iSht).Name, "Master") = 0 Then
            ThisWorkbook.Sheets(iSht).Visible = xlSheetHidden
        End If
    Next iSht
End Sub

excel - 选择“主”工作表时自动隐藏/取消隐藏“子”工作表

问题描述

解决方案

推荐阅读