Print preview multiple sheet into one Print Job (VBA)

jaeremata

New Member
Joined
Jan 20, 2021
Messages
22
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,398
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
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:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,398
Office Version
  1. 365
Platform
  1. Windows
@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.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

jasonb75
Thanks for the corrections.
 

jaeremata

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

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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

jaeremata

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

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
On what line are getting the error?

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

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,638
Members
415,849
Latest member
PhoenixRising2015

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
Top