Here's a link to a file you can download from onedrive:
https://1drv.ms/x/s!Ah_zTnaUo4DzjhWzQ3OTq9tq1APC
Rather than hard-code what should happen when each sheet is selected, I've used a ListObject (i.e. an Excel Table) on a 'Controls' sheet to store the relationships between 'parent' sheets and their various 'children'. The code simply checks this ListObject to see which children belong to which parent, and then takes action accordingly. This has the added bonus of making it very easy for someone who doesn't understand VBA in the slightest to add or amend those Parent/Child sheet relationships in need.
I've also implemented a 'Developer' mode in which the sheet hiding does NOT happen. Nothing more frustrating than trying to do development on an application that treats you like a mere 'user' :-) You can toggle it between 'User' and 'Developer' mode by using the keyboard shortcut Ctrl + Shift + D (D for Developer).
Here's how that looks in the sample file I just put together. I've added the ListObject shown below into a new sheet called 'Controls', and given the ListObject the name of 'VisibleSheets':
I've also added a named range called DeveloperMode, with a value of TRUE:
Here's the code that toggles the application between 'User' mode and 'Developer' mode, that goes in a standard code module:
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
Here's the code that actually does all the hiding and unhiding, that also goes in a standard code module:
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
Here's a snippet of code that goes in the ThisWorkbook module that assigns the Keyboard Shortcut of Ctrl + Shift + D to the ToggleDeveloperMode routine so that you can easily toggle between modes. (Don't tell users what this keyboard shortcut is):
Private Sub Workbook_Open()
Application.OnKey "^+D", "ToggleDeveloperMode"
End Sub
And lastly, here's the code triggers the DisplaySheets routine, that also goes in the ThisWorkbook module:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
DisplaySheets
End Sub
It works a treat. Here's what I see when I select each of the 3 Parent sheets in turn:
...and here's what happens when I use the Ctrl + Shift + D shortcut to put the app into 'Developer' mode, ALL sheets are unhidden, including the one with the controls on it.
I'd suggest giving the parent tabs the same color as I have here, so that it's easier for users to understand that they don't change regardless of the other tabs that selectively appear/disappear.
If there's any chance that users (or you) might want to rename the sheets, use codenames instead of sheetnames. Let me know if you're not sure what I'm talking about.
Worksheet_Activate()event to show or hide a sheet. But furthermore, you are using this event to Activate another sheet. By doing so, you are (recursively) activating the eventWorksheet_Activate()for that sheet. If there is also code on that sheet again forWorksheet_Activate()then this gets activated also (and so on). So, you might want to addApplication.EnableEvents = Falsebefore you activate a sheet in this code. Once the other sheet is activated you can then re-enable it again:Application.EnableEvents = True. – Ralph