Hide / Unhide worksheets

[scotthannaford1973]

Hi all

I have a workbook with a huge number of worksheets that are optional, depending on the work they are being used for. I have a master worksheet that lists all of the worksheets and I'd like to add a button for each worksheet on that list that will hide/unhide the wokbook depending on whether or not it's used. I can record a macro to hide, another to unhide etc but I can't work out the code to "hide worksheet if visible, unhide if workbook is hidden, no error message!)". Can anyone advise?

cheers!

 

[My Aswer Is This]

I would suggest running this script to enter all sheet names in column A of the master sheet.

Sub Enter_Worksheet_Names()
'Modified 10/21/2020 11:53:33 AM  EDT
Application.ScreenUpdating = False
Dim i As Long

For i = 1 To Sheets.Count
    Cells(i, 1).Value = Sheets(i).Name
Next
Application.ScreenUpdating = True
End Sub

Then my second script will run when ever you double click on a sheet name in column A.
The script will toggle the sheet from being visible or not visible.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/21/2020  12:21:15 PM  EDT
If Target.Column = 1 Then
Cancel = True
Sheets(Target.Value).Visible = Not Sheets(Target.Value).Visible
End If
End Sub

Glad you liked this