Excel VBA worksheet event to toggle between hide / unhide

Question

I am working on a project to minimise the number of sheets that are visible in a an excel workbook at any one given time. I am trying to create a parent sheet (which acts as a toggle button) to show/hide child sheets. For example I have a 6 sheets in a workbook: Inputs, Input 1 and Input 2, Outputs, Output 1 & Output 2. Inputs and Outputs will toggle hide and unhide the others worksheets when activated. I have created 2 worksheet level Sub Functions to try and do this. The first one works great, but the other one only works if the first one has been activated and hidden again. Any advice on a better way to do this would be great. I am not sure why excel doesn't have this function already. I tried with arrays, but that doesn't seem to work. I think you need to unhide each tab individually.

'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

I have managed to make this work. The issue was had something to do with trying to use the "Outputs" Private Sub Worksheet_Activate() function when the "Input 1" sheet was activated and hidden. I have added another tab called "Main" to replace this, so "Main" will always be the active tab after the function has been activated. This resolves the issue although it would be better if the focus didn't jump around the workbook while navigating what in each "folder". Here is the updated code...

'Inputs "Parent folder" sheet
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("Main").Activate** 'needed to deactivate Inputs sheet


End Sub

'Outputs "Parent folder" sheet
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("Main").Activate** 'needed to deactivate Outputs sheet


End Sub

I think this is a great way to simplify workbooks with may tabs. It could defiantly be improved so look forward to hearing any suggestions.

Here is a link to my working file

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

Answer 2

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.

Answer 3

As per user3598756, this question may need some clarification, however it sounds like you trying to emulate behaviour similar to this:

Action              Visible Worksheet
------              -----------------
Open Workbook       [Input], [Output]
Activate [Input]    [Input], [Output], [Input1], [Input2] ' (shows InputX)
Activate [Input1]   [Input], [Output], [Input1], [Input2] ' (no change)
Activate [Output]   [Input], [Output], [Output1], [Output2] ' (hides InputX, shows OutputX)

This makes [Input] and [Output] your only gateway worksheets, so the following on [Input] (and the converse for [Output]) would achieve this.

Private Sub Worksheet_Activate()
    Sheets("Input 1").Visible = True
    Sheets("Input 2").Visible = True
    Sheets("Output 1").Visible = False
    Sheets("Output 2").Visible = False
End Sub

Notes

1. Avoid using On Error Resume Next unless you have a specific reason to. It's usually a good thing that your code grinds to a halt if there is something wrong, as opposed to keeping its little secrets to itself and leaving you none the wiser.

2. The .Visible property is itself a Boolean, so the conditional .Visible = True is equivalent to using just .Visible by itself.

3. Your .Visible statements are probably not resolving how you intended them to. Only one of your = on each line will be an assignment operator, the others will be equality checks. In the absence of brackets, it will be your first =, and the other = will be step-by-step equality checks working from the right to the left. This is operator precedence at work.

Regarding the last point, say sheet Input 1 is visible, your first line will resolve as:

Sheets("Input 1").Visible = True = Not Sheets("Input 1").Visible = True
Sheets("Input 1").Visible = True = Not                    <True> = True
Sheets("Input 1").Visible = True = Not                        <True>
Sheets("Input 1").Visible = True = <False>
Sheets("Input 1").Visible =   <False>

Where what I believe was intended is:

Sheets("Input 1").Visible = Not Sheets("Input 1").Visible ' i.e. toggle my visibility

These things can be hard to pick up with booleans, because even if your logic is wrong, the result can be right 'half' of the time.