How do I add the ability to select the number of printed pages to aDialog Sheet

[Breck]

I found this code at http://www.j-walk.com/ss/excel/tips/tip48.htm and
it is almost perfect for my needs. I would like to add just one
option. I would like to add a spinbox or some other way to choose the
number of pages to print. The default is just 1
(ActiveWindow.SelectedSheets.PrintOut copies:=1). I would like to be
able to choose the number or pages printed. I probabaly don't need
more that 10.

What would be really cool if the code could be modified so the number
of pages could be a different number for each sheet that has a
checkbox in the dialog box. If that is just too difficult to do then
just a single choice would be find.

Any help or suggestions would be greatly appreciated

Although this procedure uses an Excel 5/95 Dialog Sheet, it will also
work with later versions of Excel. By the way, this is a rare example
of how using a Dialog Sheet is easier than using a UserForm. Creating
a UserForm on the fly is much more difficult (but it is possible --
click here for an example).
Using the code

To try out the example, copy the code below and paste it to a VBA
module (there is no need to add a dialog sheet). Then execute the
SelectSheets subroutine.
The SelectSheets subroutine

Option Explicit

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount 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

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

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

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
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
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

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

' Reactivate original sheet
CurrentSheet.Activate
End Sub

NOTE: Aaron Blood suggested the following modification, which prints
the selected sheets as a single print job. This allows the sheet to be
printed with continuous page numbers.

If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb
ActiveWindow.SelectedSheets.PrintOut copies:=1
ActiveSheet.Select
End If

 

[Gary Keramidas]

one way is to dim the variable and then add the rest at the beginning of your
loop. you will need to change what happens if some clicks cancel . this is
untested:

dim Numcop as long

' get the number of print copies for each report
Numcop = Application.InputBox("Enter number of copies to print:", _
"How Many Copies?",1, Type:=1)
If Numcop = 0 Then
then exit
ElseIf Len(Numcop) > 0 Then
End If

and then your print line
ActiveSheet.PrintOut Copies:=Numcop

 

[Breck]

Thanks Gary it works a little different than I had hoped for but might
actually be the better way. I did change the "then exit" line to skip to
just after the "ActiveWindow.SelectedSheets.PrintOut copies:=1"

After thinking about my request for selecting the number of copies on a
sheet by sheet basis I realized that that code would have to be very
complicated since I wanted the pages numbered. The code would have to not
only consider listing all of the non-hidden sheets but print each sheet
individually the number of copies chosen then moving on to the next sheet in
the list. That would mean that any page numbering in the headers and footers
have from 1 thru the number of copies chosen. I wouldn't be able to have the
sheets collated Sheet1 1 of 3, Sheet2 2 of 3, Sheet3 3 of 3 then Sheet1 1 of
3, Sheet2 2 of 3, Sheet3 3 of 3 etc but of Sheet1 1 of 3, Sheet1 2 of 3,
Sheet1 3 of 3 etc so the code would become very tricky if not nearly
impossible since the number of sheets could be different. Oh well sometimes
you just don't think thru everything all the way to the end.

"