How do you "Unhide" multiple sheets at one time?

[Don Guillett]

Use a for/each loop

 

[Gord Dibben]

Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

 

[KL]

You can also use the following structure to pick sheets one by one:

Sub ProtectSheetsSelected()
Application.ScreenUpdating = False
'You can also use names instead of indeces e.g: "Sheet1".
MyArray = Array(1, 5, 7, 12)
For Each i In MyArray
ThisWorkbook.Sheets(i).Visible=True
Next i
End Sub

Regards,
KL

 

[Vasant Nanavati]

If you want to select the sheets you want to unhide, you have to write your
own macro. Try:

Sub UnhideMultipleSheets()
Dim ws As Worksheet
Dim fHidden As Boolean
On Error GoTo ErrorHandler
fHidden = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetHidden Then
fHidden = True
Exit For
End If
Next
If fHidden Then
UnhideSheets.Show
Else
MsgBox "There are no hidden sheets.", vbInformation + vbOKOnly,
"Nothing to unhide!"
End If
Exit Sub
ErrorHandler:
Beep
End Sub

Of course, you will need a UserForm with a ListBox with its MultiSelect
property set to fmMultiSelectExtended, CommandButton1 (OK) and
CommandButton2 (Cancel), and the following code:

Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
ActiveWorkbook.Worksheets(ListBox1.List(i)).Visible =
xlSheetVisible
End If
Next
Unload Me
Exit Sub
ErrorHandler:
MsgBox "Sorry; can't be done!"
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub ListBox1_Change()
If ListBox1.ListIndex = -1 Then
CommandButton1.Enabled = False
Else
CommandButton1.Enabled = True
End If
End Sub

Private Sub UserForm_Activate()
Dim ws As Worksheet
CommandButton1.Enabled = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetHidden Then
ListBox1.AddItem (ws.Name)
End If
Next
End Sub

I may have missed something, so post back if you have a problem.