Toggle sheet visibility

[John]

How can I modify the below code to remember what sheets were
originally hidden so that it only hides those sheets? Kind of like a
"smart" toggle current hidden sheets.

For Each sh In Sheets
sh.Visible = True
Next sh

 

[Chip Pearson]

Try something like the following. Run LoadHiddenValues when you want to save
the sheet's visible properties and ShowOrHide when you want to restore the
saved visible properties.

Sub LoadHiddenValues()
Dim N As Long
Dim S As String
With ThisWorkbook.Sheets ' Sheets not Worksheets
For N = 1 To .Count
S = S & .Item(N).Name & ":" & CStr(.Item(N).Visible) & "\"
Next N
End With
S = Left(S, Len(S) - 1)
ThisWorkbook.Names.Add Name:="SheetVisible", RefersTo:="'" & S & "'",
Visible:=False
End Sub

Sub ShowOrHide()
Dim S As String
Dim V As Variant
Dim W As Variant
Dim N As Long

Application.ScreenUpdating = False
S = ThisWorkbook.Names("SheetVisible").RefersTo
S = Replace(S, Chr(34), "")
S = Replace(S, Chr(39), "")
S = Mid(S, 2)
V = Split(S, "\")
With ThisWorkbook.Sheets
For N = 1 To UBound(V)
W = Split(V(N), ":")
.Item(W(0)).Visible = W(1)
Next N
W = Split(V(0), ":")
.Item(W(0)).Visible = W(1)
End With
ErrH:
Application.ScreenUpdating = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[John]

Thanks, Chip! That gives me something to chew on...