Print preview multiple sheet into one Print Job (VBA)

jaeremata

New Member
Joined
Jan 20, 2021
Messages
24
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
Try this. Untested (so I don't f it will work) :
VBA Code:
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
 
Last edited:
Upvote 0
@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.
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 

Attachments

  • 1611215917458.png
    1611215917458.png
    12.3 KB · Views: 14
Upvote 0
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 ?
 
Upvote 0
On what line are getting the error?

Try changing this :
VBA Code:
Sheets(SheetArray).PrintPreview
To this :
VBA Code:
Sheets(Array(SheetArray)).PrintPreview
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top