MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macros for making all worksheets visible and all but one invisible


Posted by Twood on November 27, 2001 6:20 AM

Hi,
I need a macro that hides all the worksheets but one of my choice and another that makes all the sheets in the workbook visible again. Any ideas? Thanks.


Posted by Leontes on December 01, 2001 2:58 AM


If the worksheet that is to remain unhidden is always the same worksheet :-

Sub HideSheets()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "The Sheet Name" Then ws.Visible = False
Next
End Sub


If you want all worksheets except the active worksheet to be hidden :-

Sub HideSheets()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> ActiveSheet.Name Then ws.Visible = False
Next
End Sub


If you want the worksheet that is to remain unhidden to be specified via an input box :-

Sub HideSheets()
Dim sh As Variant, ws As Worksheet, x As Integer
sh = InputBox("Enter A Worksheet Name")
If sh = "" Then Exit Sub
x = 0
For Each ws In Worksheets
If ws.Name = sh Then
x = 1
Exit For
End If
Next
If x = 0 Then
MsgBox " There is no sheet named " & sh
Else
For Each ws In Worksheets
If ws.Name <> sh Then ws.Visible = False
Next
End If
End Sub


To make all worksheets visible :-

Sub UnhideSheets()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = True
Next
End Sub


Posted by Rick E on December 04, 2001 9:21 AM

Here are the 2 marcos...

Here are the macros, HideEm and ShowEm, note that with HideEm you can request a sheet that is not visible and that one shows and the one that was visible is hidden.

Sub HideEm()
' Hides all workbook sheets except one.
str1 = "Enter name of sheet not to hide."
str2 = InputBox(str1, "Hide Sheets")
i = 0
j = ActiveWorkbook.Sheets.Count
If j = 1 Then
str1 = "No sheets to hide."
j = MsgBox(str1, vbCritical + vbOKOnly, "Workbook has only one sheet.")
Exit Sub
End If
j = 0
k = 0
For Each Item In ActiveWorkbook.Sheets
str1 = Item.Name
If str1 = str2 Then
i = 1
End If
Next
If i = 0 Then
str1 = "Sheet name ( " & str2 & " ) not found! Check spelling."
i = MsgBox(str1, vbCritical + vbOKOnly, "Input Error")
Exit Sub
End If
For Each Item In ActiveWorkbook.Sheets
If Item.Visible = True Then
j = j + 1
If Item.Name = str2 Then
k = 1
End If
End If
Next
If j = 1 And k = 1 Then
str1 = "Sheet name ( " & str2 & " ) already visible."
i = MsgBox(str1, vbCritical + vbOKOnly, "Nothing to do.")
Exit Sub
End If
If j = 1 And k = 0 Then
Sheets(str2).Visible = True
End If
For Each Item In ActiveWorkbook.Sheets
str1 = Item.Name
If str1 <> str2 Then
If Sheets(str1).Visible = True Then
Sheets(str1).Visible = False
End If
End If
Next
End Sub

Sub ShowEm()
For Each Item In ActiveWorkbook.Sheets
str1 = Item.Name
Sheets(str1).Visible = True
Next
End Sub

I did this just before the message board problem, hope you have looked back and get these.