Hiding all sheets except the active one

[ente]

I have a workbook with nearly 100 sheets. Each sheet has a hyperlink to each other sheet. What I would like to have is only one tab showing, which is for the active sheet. When I hyperlink to another page (sheet) I would like the page I am leaving be hidden and the page I am going to to be un-hidden. I would like to do this just to clean up the look of my project and not have 100 tabs displayed at the bottom. I can do this with some success by returning to a "master" page that then hides all sheets except itself but that does not let me roam from page to page without leaving un-hidden tabs on the bottom of the sheet. Any ideas would be appreciated. Thanks.

 

[JLGWhiz]

If you do a one time manual exercise to hide all but one sheet, then put this in the ThisWorkbook code module, it should work like you want.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Sh.Visible = False
End Sub

 

[ente]

Thanks for taking the time to respond. I am still slowly working on my project and hope this will help.

 

[JLGWhiz]

You're welxome,
Regards, JLG

 

[VBasic2008]

Here is how I managed to do it including two other useful procedures.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    On Error Goto ErrorHandler
    Me.Worksheets(Target.Name).Visible = xlSheetVisible
    Sh.Visible = xlSheetHidden
ErrorHandler:
End Sub

Sub hideWorkSheetsExceptFirst()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    If wb.Worksheets.Count = 0 Then Exit Sub
    Dim ws As Worksheet
    With CreateObject("Scripting.Dictionary")
        For Each ws In wb.Worksheets
            If ws.Index > 1 Then
                .Add ws.Name, Empty
            End If
        Next ws
        If .Count = 0 Then Exit Sub
        Application.ScreenUpdating = False
        wb.Worksheets(1).Hidden = xlSheetVisible
        wb.Worksheets(.Keys).Hidden = xlSheetHidden
        Application.ScreenUpdating = True
    End With
End Sub

Sub showAllWorkSheets()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    If wb.Worksheets.Count = 0 Then Exit Sub
    Dim ws As Worksheet
    With CreateObject("Scripting.Dictionary")
        For Each ws In wb.Worksheets
            If ws.Visible = xlSheetHidden Then
                .Add ws.Name, Empty
            End If
        Next ws
        If .Count = 0 Then Exit Sub
        Application.ScreenUpdating = False
        wb.Worksheets(.Keys).Hidden = xlSheetVisible
        Application.ScreenUpdating = True
    End With
End Sub

 

[JLGWhiz]

Thanks for the feedback.

Just a comment: Excel will not allow you to have a workbook with zero sheets. There must be at least 1 sheet in a workbook by default.
Regards, JLG