Check Box to print in order

[ABabeNChrist]

I use a Check Box method to print different sheets within same Workbook
I have 18 Check Boxes. I am using this code below and it does print, but I noticed if one sheet has a lot more data or photos than another it takes a longer to send to printer and in turn causes it to lag behind, at which it causes it to fall out of order. Is there a way to keep it in numeral order.

Code: Select all

If CheckBox1.Value = True Then
 Worksheets("Cover Page").Visible = xlSheetVisible
 Worksheets("Cover Page").Select
     Range("A1:AM105").PrintOut Copies:=1, Collate:=True
     Worksheets("Cover Page").Visible = xlSheetHidden
    End If

[HansV]

Unlike Word, Excel does not have a setting to turn off background printing. So I fear you'll have to build in a pause after each PrintOut command.

[ABabeNChrist]

Hi HansV
As it stands right now, when I check desired sheets using a check box, then select print command button my selected sheets are sent to my default printer, which is PDF Creator. From there it loads all files to be printed. I can re-organize the print order from there, but all the file names are the same, they are names after the workbook.

[HansV]

I don't know how to control PDFCreator. You may have to copy each sheet that is to be printed to a new workbook, save the new workbook, print it to PDF then discard it. But hopefully someone will come up with a less cumbersome suggestion.

[ABabeNChrist]

Hi HansV
You mentioned on post# 4786 about building in a pause after each PrintOut command. What might that be

[HansV]

You could insert an instruction

Application.Wait Now + TimeSerial(0, 0, 5)

after the line with PrintOut. This will make Excel pause for 5 seconds. You can change the 5 to another number, of course.

[ABabeNChrist]

Thank you HansV
I'll load up a sheet with a large amount of data, then time it and apply that to the time between sheets.
it may take a few seconds longer, I can live with that

[sdckapr]

Does the code at: http://excelguru.ca/node/21#MultiMulti help

Steve

[ABabeNChrist]

Does the code at: http://excelguru.ca/node/21#MultiMulti help

Steve

Hi Steve
Thank you,I gave it a quick look over; I’ll give it a try, nothing to lose.

[ABabeNChrist]

Does the code at: http://excelguru.ca/node/21#MultiMulti help

Steve

Hi Steve
Thank you,I gave it a quick look over; I’ll give it a try, nothing to lose.

I came across this thread
http://www.excelforum.com/excel-program ... -file.html
I have been mulling it over trying to see if I could adapt the PDF code to my check boxes. I understand some of what the code is showing but for the most part it is beyond my ability.

[HansV]

The code Steve pointed you to will print several sheets to individual PDF files.
The code in the link you provide is intended to print several sheets into a single PDF file.
What exactly do you want to accomplish?

[ABabeNChrist]

Hi HansV
I checked out the code that steve provided and it was refering to and older version of PDF Creator, it refered to 0.9.0.
I have 0.9.3. and the new version 0.9.9. I tried to apply code to see if it may work, but i got error message refering to PDF Creator type, So I thought lets search around a little more and that's when I found this other code.
And yes I am trying to print multiple sheets into 1 PDF file.
I also tried the timer method that you provided and it did seem to work, but if I have a few sheets to print it can take a while, and I was looking into other avenues to speed up the process.

[HansV]

See if you can adapt the code in the attached text file for your purpose. It's based on the code by Ken Puls - please leave his name in there.

Print2PDF.txt

[ABabeNChrist]

HansV thank you very much for your time, patience and expertise
It works really good, cuts a lot of corners and saves time.

[ABabeNChrist]

Hi HansV
As I mentioned in my last post it "It works really good" that is true except when I tried to increase the check boxes beyond 5, it would then not run through the code completely, at least that’s how it appears. Now what it does is when I select, let’s say 10 check boxes I can see it going through each of the sheets, when it seems to finish the last sheet, it just seems to stop. The PDF creator is still open, but just hasn’t started combining the file into one, it seems to stop there. When I first received the code from you I set it up with 5 check boxes and it worked better than I could have hoped for.
Then I populated it to 18 check boxes, I doubled checked for any spacing or any misspelling. Everything looked good. It just won’t seem to work, I played around with it for awhile thinking maybe I missed something.
Below I have attached my code

[HansV]

Your code looks OK. I fear that you're back at a timing problem. Apparently the PDFPrinter cannot handle so many requests in a short time.

[ABabeNChrist]

H HansV
I also thought it could be the timing, so I tried

Code: Select all

Application.Wait Now + TimeSerial(0, 0, 10)

With no such luck. I even uninstalled then reinstalled PDF Creator, No change. Another strange thing is, even thought I may only check 3 of the 18 check boxes it still will not complete the cycle. I have to resort back to the original that only has a couple check boxes, and then it operates great.

[rory]

FYI, this now seems to be continued here.

[Don Wells]

Have you considered:

• placing the sheets to be printed in the printing order;
  selecting the sheets to be printed;
  printing the selected sheets with one print command;
  re-ordering the sheets to the default order.

[ABabeNChrist]

Hi Don
I not sure I fully unserstand what you mean.
I do have the check boxes in the order I wish to print, so if one check box is not checked it would go to next check box, so basically the numeral order through the check boxes is correct. it just seems that some just lag behind, possibly because of the size, not really sure. Now if there was any possible way to view each sheet name when viewing the PDF Creator, but at the present here's all I see. Each sheet has the name of the workbook. If I knew the names I could then change there order, then I could combine them to make a single report, that would work