如何实现选中Master工作表时自动显示对应Sub工作表，否则隐藏？

Instead of manually adding code to every generated sheet (which is tedious when using macros to create sheets), you can use a workbook-level event handler to manage all sheet visibility in one place. This single piece of code will automatically handle showing/hiding Sub sheets whenever you switch between worksheets.

Step 1: Access the Workbook Module #

• Press Alt + F11 to open the VBA Editor.
• In the Project Explorer pane (left side), double-click ThisWorkbook under your workbook's name.

Step 2: Paste the Workbook Event Code #

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim ws As Worksheet
    Dim targetGroup As String
    Dim isMasterSheet As Boolean
    
    ' Start by hiding all Sub sheets globally
    For Each ws In ThisWorkbook.Worksheets
        If InStr(ws.Name, "Sub") > 0 Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
    
    ' Check if the activated sheet is a Master sheet
    isMasterSheet = (InStr(Sh.Name, "Master") > 0)
    If isMasterSheet Then
        ' Extract the group number from the Master sheet name (e.g., "Group 3 Master" → "3")
        targetGroup = Split(Split(Sh.Name, "Group ")(1), " Master")(0)
        
        ' Show all Sub sheets linked to this group
        For Each ws In ThisWorkbook.Worksheets
            If InStr(ws.Name, "Group " & targetGroup & " Sub") > 0 Then
                ws.Visible = xlSheetVisible
            End If
        Next ws
    End If
End Sub

How This Code Works #

• Default Hide All: Every time you switch to any sheet, all sheets with "Sub" in their name are hidden first.
• Master Sheet Trigger: If you activate a Master sheet (identified by "Master" in its name), the code extracts its group number, then finds and shows all Sub sheets belonging to that group (e.g., activating "Group 2 Master" will show "Group 2 Sub 1", "Group 2 Sub 2", etc.).
• Non-Master Behavior: If you switch to a non-Master sheet (like another Master group or a standalone sheet), all Sub sheets remain hidden.

Tips for Your Sheet-Generating Macro #

• Ensure your macro creates sheet names strictly following the Group X Master and Group X Sub Y format (spaces and keywords matter for the code to detect groups correctly).
• If you want to adjust the naming convention (e.g., use "Team" instead of "Group"), just update the string checks in the code (replace "Group " with "Team ").

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