Use a for/each loop

--
Don Guillett
SalesAid Software

"RPF2407" wrote in message
...

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

On Sat, 25 Dec 2004 13:11:02 -0800, "RPF2407"
wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Sat, 25 Dec 2004 13:11:02 -0800, "RPF2407"
wrote:

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)).Visibl e =
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.

--

Vasant