VBA hide/unhide tabs

[migoreng86]

Hi,

I'm pretty new to VBA and have skipped the basics in order to 'get the job done' for this one.


Basically, I have a spreadsheet with multiple tabs (around 60). These are broken down to a cover sheet, a contents sheet (with hyperlinks to every tab) and 4 or 5 expandable/collapsible summary sheets that when expanded, have a number of sub-tabs.

Private Sub Worksheet_Activate()
    Dim sheet As Worksheet
    Application.ScreenUpdating = False
    If ShowHide.Name = "Show My Guts" Then
        'Make all sheets visible
        For Each sheet In ThisWorkbook.Sheets
            sheet.Visible = xlSheetVisible
        Next sheet
        'Change the sheet name to the "Collapse" name you want
        ShowHide.Name = "Hide My Guts"
        'Pick a sheet to display after the once hidden sheets are expanded
        Sheet4.Activate
    Else
        'Hide all sheets except the one's you want to keep visible
        For Each sheet In ThisWorkbook.Sheets
            If (sheet.Name <> Results.Name And sheet.Name <> Run.Name And sheet.Name <> ShowHide.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet
        'Change the sheet name to the "Expand" name you want
        ShowHide.Name = "Show My Guts"
        'Pick a sheet to display after the sheets to be hidden are collapsed
        Run.Activate
    End If
    Application.ScreenUpdating = True
End Sub

I've managed to successfully google my way to be able to expand/collapse the summary sheets using the above code. Also, I've been able to show a hidden hyperlinked sub-tab with the code below, and to make that sub-tab re-hide when the sheet is deactivated (bottom code).

[SIZE=2]Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)[/SIZE]
[SIZE=2]    LinkTo = Target.SubAddress[/SIZE]
[SIZE=2]    WhereBang = InStr(1, LinkTo, "!")[/SIZE]
[SIZE=2]    If WhereBang > 0 Then[/SIZE]
[SIZE=2]        MySheet = Left(LinkTo, WhereBang - 1)[/SIZE]
[SIZE=2]        MySheet = Application.WorksheetFunction.Substitute(MySheet, "'", "")[/SIZE]
[SIZE=2]        Worksheets(MySheet).Visible = True[/SIZE]
[SIZE=2]        Worksheets(MySheet).Select[/SIZE]
[SIZE=2]        MyAddr = Mid(LinkTo, WhereBang + 1)[/SIZE]
[SIZE=2]        Worksheets(MySheet).Range(MyAddr).Select[/SIZE]
[SIZE=2]        End If[/SIZE]
[SIZE=2]End Sub[/SIZE]

Private Sub Worksheet_Deactivate()
Me.Visible = False
End Sub

However this also means that when a summary tab is expanded and it's hidden sub-tabs are visible, the individual sub-tabs also disappear when I click amongst them.


So, what I REALLY need is for the sub-tabs to:
- remain visible when clicking through them if they have been selected after expanding their summary sheet.
- return to hidden after clicking away from them if they have been selected from the hyperlink contents page.


I've been looking into the possibility of using a Workbook_SheetDeactivate to capture the sheet name as 'lastSheet' and using that to determine if the last sheet is visible or hidden. No joy as yet though so I'm taking to posting.


The code examples above are taken from forum posts as the sheets I'm working with contain sensitive data.

I hope this makes sense (it probably doesn't) and I would appreciate any help.

Thanks,
Kevin

 

[mole999]

I think this is your culprit

Private Sub Worksheet_Deactivate()
Me.Visible = False
End Sub

when you take your focus from the active tab, you tell it to close

 

[migoreng86]

Hi mole,

Thanks for getting back.

Yes this is the culprit but in a way it answers half the problem too. I do want to close the active tab when I take the focus from it, but only in certain cases ie...

Case to hide: when I click back to a contents tab.
Case to stay unhidden: When I click another sub-tab.

I've been trying (unsuccessfully) to take my logic literally and apply case statements but no luck just yet.

 

[mole999]

So you need (I think) to turn the logic round.

On the worksheet activate event, check what is open and then close those you don't need, at least you won't trigger the auto hide

 

[carstenp]

Can anyone help? I'm getting a Run-time error '464' Object Required at the ShowHide.Name = "Show My Guts" line. Is "ShowHide" an official function? I tried thisworkbook.worksheets.name and that got me through a ways, but it got hung up again. Thanks

 

[mole999]

I can't find showhide as being Excel, you can at the top of the code do

Dim ShowHide

which makes it usable

A quick point

You cannot hide all the sheets as it will fail, you have to have atleast one showing, so if its not visible, make it visible then hide the sheet you want to hide

 

[carstenp]

Thanks mole!

 

[trugbee1203]

Apologies for bringing this up again, but i had the same issue that carstenp had with the Run-time error '464' Object Required at the ShowHide.Name = "Show My Guts" line. Does anyone know how to resolve this? Would really appreciate the help.

Thanks!