View Full Version : Printing Copies with VBA

02-14-2011, 11:54 AM
I have been working on the following script in a user form that allows the user to print certain sheets in the workbook and uses an array of checkboxes and an array of textboxes. The checkboxes correspond with sheet names in the workbook. The textboxes correspond with the checkboxes and provide an area for the user to enter the number of copies they would like to print.

To me the script looks like it is working correctly except that when the PrintOut command is called it dosent print multiple copies of the sheets the user requested copies of.

When I use the locals window to see the values in arr2() array just before the PrintOut command is called it has all the correct values in it.

example of arr2() values before PrintOut command:

user want 3 copies of sheet1 and 1 copy of sheet2

arr2(1) = "sheet1"
arr2(2) = "sheet1"
arr2(3) = "sheet1"
arr2(4) = "sheet2"

When Printing Out (arr2) it only prints one copy of sheet1 and one copy of sheet2.

What am I doing wrong hear? How can I get this script to print sheet1 3 time and sheet2 once?

Private Sub CommandButton1_Click()
Dim wks As Worksheet
Dim arr2() As String
Dim T, M, CopyNumber, CopyCount As Integer
T = 0

CheckBoxing = Array(ckSheet1, ckSheet2, ckSheet3, ckSheet4)
Text = Array(txtSheet1, txtSheet2, txtSheet3, txtSheet4)

For Each wks In ActiveWorkbook.Worksheets
For M = 0 To UBound(CheckBoxing)
If CheckBoxing(M).Value = True And CheckBoxing(M).Visible = True And wks.Name = CheckBoxing(M).Caption Then
CopyCount = 0
CopyNumber = Text(M).Value
T = T + 1
CopyCount = CopyCount + 1
ReDim Preserve arr2(1 To T)
arr2(T) = wks.Name
Loop Until CopyCount = CopyNumber
End If
Next M
Next wks

Application.EnableEvents = False
With ActiveWorkbook
End With
Application.EnableEvents = True

End Sub

02-14-2011, 12:36 PM
If it helps any I have attached a dummy workbook that includes the userform and the problem.

02-14-2011, 12:51 PM
Hi Austin,

without having tried it, I see here that you need to loop through the array arr2 like
With ActiveWorkbook
For M=0 to UBound(arr2)
Next M
End With

Another remark: your dims in this line
Dim T, M, CopyNumber, CopyCount As Integer
are working, but don't exactly do what I assume you want.
If all the variables are meant to be integers, you should code:
Dim T As Integer, M As Integer, CopyNumber As Integer, CopyCount As Integer