ExcelBanter - Hide/Unhide sheets

Hi Ali,

The code to make the sheets visible is something like

For Each sh In Activeworkbook.Sheets
sh.Visible = xlVisible
Next sh

As for making your life easier, try this little technique we got from John
Walokenbach. It throws up a print dialog sheet with all of the sheet names,
but it doesn't print. Just change the msgbox for your code.

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim iBooks As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

iBooks = 0

' Add the checkboxes
TopPos = 40
With ActiveWorkbook
For i = 1 To .Sheets.Count
If .Sheets(i).Name < PrintDlg.Name Then
Set CurrentSheet = .Sheets(i)
' Skip empty sheets and hidden sheets
iBooks = iBooks + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(iBooks).Text = _
Sheets(i).Name
TopPos = TopPos + 13
End If
Next i
End With

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Reactivate original sheet
CurrentSheet.Activate
PrintDlg.Visible = False

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to process"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
MsgBox Sheets(cb.Caption).Name & " selected"
End If
Next cb
Else
MsgBox "Nothing selected"
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ali " wrote in message
...
Hi everyone, can anyone help with the following please,

I often hide a lot of sheets before sending them to my boss - he's only
interested in the final report.

Is it possible to insert a button into the sheet that will indicate to
my boss that there are hidden sheets and should he so wish he can click
on it and have a list of the hidden sheets appear. From this he can
select the sheet or sheets he wants to see?

Is this possible?

Also, to speed up my hiding of sheets is it possible to devise a macro
that will list all of the sheets in the book. I will then be able to
select all the sheets i want to hide and have it done on clicking on
'ok'.

Many thanks for your input guys

---
Message posted from http://www.ExcelForum.com/