VBA hide/unhide tabs

migoreng86

New Member
Joined
Mar 12, 2014
Messages
2
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.

Code:
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).

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]

Code:
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

Moderator
Joined
Oct 23, 2004
Messages
9,899
I think this is your culprit

Code:
Private Sub Worksheet_Deactivate()
Me.Visible = False
End Sub
when you take your focus from the active tab, you tell it to close
 

migoreng86

New Member
Joined
Mar 12, 2014
Messages
2
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

Moderator
Joined
Oct 23, 2004
Messages
9,899
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

Board Regular
Joined
May 27, 2010
Messages
148
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

Moderator
Joined
Oct 23, 2004
Messages
9,899
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
 

trugbee1203

New Member
Joined
Mar 4, 2015
Messages
1
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!
 

Forum statistics

Threads
1,082,323
Messages
5,364,589
Members
400,808
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top