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!

 

[Fluff]

How about

   With Sheets("Sheet1")
      .Visible = Not .Visible
   End With

 

[Fluff]

If any of the sheets could be very hidden, use

   With Sheets("Sheet1")
      .Visible = Choose(.Visible + 2, 0, -1, , -1)
   End With

 

[scotthannaford1973]

Thanks, Fluff - unfortunately when the worksheet is hidden, it brings up a run-time error; it also doesn't unhide the workbook if it is already hidden. I'd basically like one button that both/either hides or unhides a named worksheet.

 

[Fluff]

Which code are you referring to?

 

[scotthannaford1973]

Hi - actually this appears to work - thanks

Sub HideUnhideBCE()


If Sheet5.Visible Then
Sheet5.Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("HOME").Select
Else
Sheet5.Visible = True
End If
End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[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

 

[Fluff]

@My Aswer Is This
That code will fail if the sheet is very hidden.

 

[scotthannaford1973]

@My Aswer Is This
That code will fail if the sheet is very hidden.

Hi Fluff

yes that's fine - I only use hidden on the worksheets.

Appreciate your input!