How do you "Unhide" multiple sheets at one time?
|
|
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 |
All times are GMT +1. The time now is 09:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com