ExcelBanter - printing and viewing

BoB Philips just posted his adaptation of John Walkenbach's code in response
to another question. It handles the printing. You can adapt it for viewing
as well.

Sub PrintSheets()
Const nPerColumn As Long = 35 'number of items per* column
Const nWidth As Long = 7 'width of each lette*r
Const nHeight As Long = 18 'height of each row
Const sID As String = "___WorksheetPrint" 'name of dialog shee*t
Const kCaption As String = " Select worksheets to print"
'dialog caption

Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cLeft As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim iLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox

Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40

For i = 1 To ActiveWorkbook.Worksheets.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.ActiveSheet
cLetters = Len(ActiveWorkbook.Worksheets(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Caption = ActiveWorkbook.Worksheets(i).Name
TopPos = TopPos + 13

Next i

.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.CheckBoxes
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).PrintOut
End If
Next cb
Else
MsgBox "No sheets selected"
End If
Application.DisplayAlerts = False

.Delete

End With

End Sub

--
Regards,
Tom Ogilvy

"Sam" wrote in message
...
Hi I am quite new too!

I would like to know if there is a way to set up a combo
to go to any of the worksheets in my file. I have a
growing number of them! I always either view or print
them so if I can use the same box but two buttons - one
to print and one to view then that will be even better.

Can anyone help with this.

Sam