austin350s10
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
Do
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
.Worksheets(arr2).PrintOut
End With
Application.EnableEvents = True
End Sub
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
Do
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
.Worksheets(arr2).PrintOut
End With
Application.EnableEvents = True
End Sub