Hiding all sheets except the active one

ente

New Member
Joined
Aug 29, 2020
Messages
2
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Sh.Visible = False
End Sub
 

ente

New Member
Joined
Aug 29, 2020
Messages
2
Office Version
  1. 2007
Platform
  1. Windows
Thanks for taking the time to respond. I am still slowly working on my project and hope this will help.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You're welxome,
Regards, JLG
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
Here is how I managed to do it including two other useful procedures.

VBA Code:
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
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,185
Messages
5,623,250
Members
415,957
Latest member
Newguy1924

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top