Print Macro

[LaraBee]

I want to print an array of sheets that are selected by check boxes so
the pages print in order. Here is my code, but I get an error in the
array, can someone help with this? Thanks!!

Private Sub Cmd_Print_Click()

Dim A As String
Dim B As String
Dim C As String

If CheckBox1.Value = True Then
A = "Sheet1"
End If
If CheckBox2.Value = True Then
B = "Sheet2"
End If
If CheckBox3.Value = True Then
C = "Sheet3"
End If

F = A & B & C

Sheets(Array(F)).Select
ActiveWindow.SelectedSheets.PrintOut

End Sub

 

[Dave Peterson]

Are these checkboxes on a worksheet (from the Control Toolbox toolbar) or are
they on a UserForm (designed in the VBE)?

If they're on a worksheet:

Option Explicit
Private Sub Cmd_Print_Click()
Dim shtCtr As Long
Dim myArr() As String
Dim selCtr As Long

selCtr = -1
For shtCtr = 1 To 3
If Me.OLEObjects("checkbox" & shtCtr).Object.Value = True Then
selCtr = selCtr + 1
ReDim Preserve myArr(0 To selCtr)
myArr(selCtr) = "Sheet" & shtCtr
End If
Next shtCtr

If selCtr = -1 Then
MsgBox "None selected"
Else
Worksheets(myArr).PrintOut preview:=True
End If

End Sub

If they're on a UserForm, change this line:
If Me.OLEObjects("checkbox" & shtCtr).Object.Value = True Then
to
If Me.Controls("checkbox" & shtCtr).Value = True Then

 

[LaraBee]

Thanks for your response. It is on a worksheet, not a form. The only
problem is that the real sheets and checkboxes are not named 1,2,3,etc.
I just posted that way for simplicity. How would I set this up if the
sheets and checkboxes just have random text names?

 

[Dave Peterson]

Don't use random names for the checkboxes. Use checkbox1, checkbox2, ...

And put the name of the worksheet in the caption of each checkbox. Then the
macro can pick up the name from that caption.

Option Explicit
Private Sub Cmd_Print_Click()
Dim shtCtr As Long
Dim myArr() As String
Dim selCtr As Long
Dim OLEObj As OLEObject

selCtr = -1
For shtCtr = 1 To 3
Set OLEObj = Me.OLEObjects("checkbox" & shtCtr)
If OLEObj.Object.Value = True Then
selCtr = selCtr + 1
ReDim Preserve myArr(0 To selCtr)
myArr(selCtr) = OLEObj.Object.Caption
End If
Next shtCtr

If selCtr = -1 Then
MsgBox "None selected"
Else
Worksheets(myArr).PrintOut preview:=True
End If

End Sub

 

[LaraBee]

PERFECT!!!!

Thank you so much!

 

[Dave Peterson]

Woohoo!!!

Glad it worked for you.

PERFECT!!!!

Thank you so much!