Print preview multiple sheet into one Print Job (VBA)

[jaeremata]

have a check box that has 4 sheets. It's working if I select 1 sheet and it's doing it's job.

But I'm having issue when 2 checkbox is selected. It open separate print job. What I want to happen is when when 2 sheets(or more) are selected on the checkbox, it will do a print preview but on one print job.

I don't know what I'm missing, please kindly point me or guide me to correct what needs to be done.

Here's my sample code:

Private Sub CommandButton1_Click()
If Me.cbSH1 = True Then
ThisWorkbook.Sheets("NC Performance").PrintPreview
End If

If Me.cbSH2 = True Then
ThisWorkbook.Sheets("8D Performance").PrintPreview
End If

If Me.cbSH3 = True Then
ThisWorkbook.Sheets("Attack Plan").PrintPreview
End If

If Me.cbSH4 = True Then
ThisWorkbook.Sheets("Plant Overview").PrintPreview
End If

If Me.cbSH1 And Me.cbSH2 = True Then
Sheets(Array("NC Performance", "8D Performance")).PrintPreview
End If
End Sub

 

[jasonb75]

Welcome to the MrExcel forum

As you asked for pointers / guidance, I've made a few notes for you to try and correct it yourself, but I, or another forum member will gladly provide further assistance if needed.

The way that you have written your code, it should do each as a single job then both together afterwards. Because you are looking at each individual box to see if it is checked, the code is doing just that, although 2 are checked it is looking at them individually, not together.

For your method to wok, you would need to start with the 4 sheets first, then go to blocks of 3 sheets, then blocks of 2 and individual ones last.

You would also need to use Else instead of End If each time, with just a single End If at the end, or Exit Sub after the print preview.

There will be ways to do the same with less code but I've tried to keep it as close as possible to what you have already tried so as not to make it too confusing.

 

[footoo]

Try this. Untested (so I don't f it will work) :

Sub Print_Sheets()
Dim x&, c&, SheetArray() As String
Dim AllSheets(): AllSheets = Array("NC Performance", _
    "8D Performance", "Attack Plan", "Plant Overview")
For x = 1 To 4
    If Me.cbSH & x = True Then
        ReDim Preserve SheetArray(c)
        SheetArray(c) = AllSheets(x - 1)
        c = c + 1
    End If
Next
Sheets(SheetArray()).PrintPreview
End Sub

 

[jasonb75]

@footoo, there are a couple of things in there that don't quite look right, I've done a quick edit to what I think it should be but it is still untested.

Private Sub CommandButton1_Click()
Dim x&, c&, SheetArray() As String
Dim AllSheets(): AllSheets = Array("NC Performance", _
    "8D Performance", "Attack Plan", "Plant Overview")
For x = 1 To 4
    If Me.Shapes("cbSH" & x) = True Then
        ReDim Preserve SheetArray(c)
        SheetArray(c) = AllSheets(x - 1)
        c = c + 1
    End If
Next
Sheets(SheetArray).PrintPreview
End Sub

I had something similar ready to post if the OP needed it, but I didn't loop mine, yours is more concise.

 

[footoo]

jasonb75
Thanks for the corrections.

 

[jaeremata]

Hi, thank you for the help. But when I tried both code, it's not working. I don't know what I need to do.

 

[footoo]

Any error message? If so, what line?
Are the checkboxes on a user form or on the worksheet?
Are they from Forms controls or ActiveX controls?

 

[jaeremata]

Hi thank you for the quick response and help. I created the checkbox as userform.,in may excel, I have a button that will call the user form, and when I select any of the sheets on the checkbox and hit preview, I will go to print preview screen.

 

[jaeremata]

When I tried to used the code that you provide it say method or data member not found. I tried to edit some of it and to be honest, I'm lost ?

 

[footoo]

On what line are getting the error?

Try changing this :

Sheets(SheetArray).PrintPreview

To this :

Sheets(Array(SheetArray)).PrintPreview