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