Outdoorsman80
Board Regular
- Joined
- Oct 4, 2014
- Messages
- 61
- Office Version
- 365
- Platform
- Windows
I have a large Excel file with many work sheets. I want to hide the data sheets and keep only certain dashboards open. I created an Index sheet to link to the hidden ones.
The code in the Index:
The code in the hidden sheets to re-hide them upon exit:
It works fine for the worksheets that don't have spaces or & in the title; but for the ones that do, I get the following error:
The line that the debug hightlights is:
The sheet linking to is hidden, it's supposed to un-hide it while you're in it.
The code in the Index:
VBA Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
End Sub
The code in the hidden sheets to re-hide them upon exit:
VBA Code:
Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetHidden
End Sub
It works fine for the worksheets that don't have spaces or & in the title; but for the ones that do, I get the following error:
Run-time error '9' Subscript out of range
The line that the debug hightlights is:
VBA Code:
Sheets(ShtName).Visible = xlSheetVisible
The sheet linking to is hidden, it's supposed to un-hide it while you're in it.