Toggle sheet visibility

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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)
 
Back
Top