I have an userform that has check boxes where user can decide which worksheets to print.
Code in userform:
Sub PrintOK_Click()
PrintSelection = Summary.value + _
CCSummary.value + _
DivSalaries.value + _
CCSalaries.value + _
PrintAll.value _
' Displays warning box if no report is selected
If PrintSelection = 0 Then
MsgBox "No Report(s) selected. Please select Report(s) to print.", 0 + vbExclamation, "Incomplete Request"
End If
' Check to see which worksheets have been selected and
' Run print modules for selected worksheets
If Summary.value = -1 Then
Call setup("Summary")
End If
If CCSummary.value = -1 Then
Call setup("Monthly CC Sum")
End If
If DivSalaries.value = -1 Then
Call setup("Div Sal Sum")
End If
If CCSalaries.value = -1 Then
Call setup("CC Sal Sum")
End If
If PrintAll.value = -1 Then
Call setup("PrintAll")
End If
' Close Form box
End
End Sub
Userform selection is passed into the following. Takes the worksheet(s) selected and stores into array.
Sub setup(Optional tabs As String)
'store sheets into array
Dim arr() As Variant
'called from printer icon on activesheet
If tabs = "" Then
arr = Array(ActiveSheet.Name)
'called via checkbox from print menu
ElseIf tabs = "PrintAll" Then
arr = Array("Summary", "Monthly CC Sum", "Div Sal Sum", "CC Sal Sum")
Else
arr = Array(tabs)
End If
Call goPrint(arr)
End Sub
Which then passes the array to another sub to do the printing
Sub goPrint(tabArr As Variant)
Dim ws As Worksheet
Dim area As String
Dim title As String
'loop through array to print each sheet
For i = 0 To UBound(tabArr)
Set ws = Worksheets(tabArr(i))
With ws
If .Name = "Summary" Then
area = "PRNSUMMARY"
title = "$1:$9"
ElseIf .Name = "Monthly CC Sum" Then
area = "PRNMONTHLYCCSUM"
title = "$2:$16"
ElseIf .Name = "Div Sal Sum" Then
area = "PRNDIVSALSUM"
title = ""
ElseIf .Name = "CC Sal Sum" Then
area = "PRNCCSALSUM"
title = "$2:$16"
End If
With .PageSetup
.PrintArea = area
.PrintTitleRows = title
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = Year(Now()) & " Budget: &F"
.CenterFooter = "Page &P of &N"
.RightFooter = "&D: &T"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
'.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
'Debug.Print tabArr(i)
'Debug.Print area
.PrintOut Copies:=1, Collate:=True
End With
Next
End Sub
There's a delay of maybe two seconds after pressing OK before the printer starts to print. The previous (much longer) code had no discernible delay. How to improve speed?
(Previous code had a different sub for each worksheet thus repeating the pagesetup
code block multiple times.)
Summary
andCCSummary
in the userform code? I'd love to see the entire procedure there... Also, aresetup
andgoPrint
written in separate modules? \$\endgroup\$ – Mathieu Guindon♦ Jun 1 '16 at 14:41setup
andgoprint
are in the same module. Obviously, code forprintok_click
is inside the userform (separate module). \$\endgroup\$ – findwindow Jun 1 '16 at 15:07