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 wrote:
>
> 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
>
> --
> LaraBee
> ------------------------------------------------------------------------
> LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275
> View this thread: http://www.excelforum.com/showthread...hreadid=499436

--

Dave Peterson

[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 wrote:
>
> 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?
>
> --
> LaraBee
> ------------------------------------------------------------------------
> LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275
> View this thread: http://www.excelforum.com/showthread...hreadid=499436

--

Dave Peterson

[LaraBee]

PERFECT!!!!

Thank you so much!

[Dave Peterson]

Woohoo!!!

Glad it worked for you.

LaraBee wrote:
>
> PERFECT!!!!
>
> Thank you so much!
>
> --
> LaraBee
> ------------------------------------------------------------------------
> LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275
> View this thread: http://www.excelforum.com/showthread...hreadid=499436

--

Dave Peterson