Check Box to print in order

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Check Box to print in order

Post by Don Wells »

You are currently sending seven or so jobs to the print queue--one for each worksheet. I am suggesting that you send only one job which prints all active worksheets.
Regards
Don

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

Hi Don
How do I go about achieving this? And will this work with hidden sheets also.
I tried using

Code: Select all

ActiveWindow.SelectedSheets.PrintOut
But it only seem to print the visible sheet
Any helpful suggestion is greatly appreciated, I have been running around in circles for days now trying to get it to function smoothly.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check Box to print in order

Post by HansV »

You have to select the sheets that you want to print; if one sheet is selected, the line

ActiveWindow.SelectedSheets.PrintOut

will print only that sheet, obviously. You could select multiple sheets by replacing

Worksheets("Cover Page").Select
Range("A1:AM105").PrintOut Copies:=1, Collate:=True
Worksheets("Cover Page").Visible = xlSheetHidden

with

Worksheets("Cover Page").Select Replace:=False

and so on for the other sheets. You'd have to hide the sheets AFTER the line

ActiveWindow.SelectedSheets.PrintOut
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

When I run the code I get an error on the code that hides all open sheets.

Code: Select all

ActiveSheet("Cover Page", "Client Information", "Summary", "Additional Photos").Visible = xlSheetHidden
I also tried ActiveWindow, ActiveWorkbook, WorkSheets and sheet

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check Box to print in order

Post by HansV »

ActiveSheet is always a single sheet, so you can't use ActiveSheet(...).
To hide or unhide multiple sheets at once, you must use Worksheets with an array:

WorkSheets(Array("Cover Page", "Client Information", "Summary", "Additional Photos")).Visible = xlSheetHidden
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

I'm still getting an error on same line
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check Box to print in order

Post by HansV »

At least one sheet must remain visible. If you try to hide all sheets, you'll get an error message.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

Here is the code I was using
I Hide the activesheet "_" before print and then unhide after

Code: Select all

If CheckBox1.Value = True Then
Worksheets("Cover Page").Visible = xlSheetVisible
Worksheets("Cover Page").Select Replace:=False
End If

If CheckBox2.Value = True Then
Worksheets("Client Information").Visible = xlSheetVisible
Worksheets("Client Information").Select Replace:=False
End If

If CheckBox5.Value = True Then
Worksheets("Summary").Visible = xlSheetVisible
Worksheets("Summary").Select Replace:=False
End If

If CheckBox6.Value = True Then
Worksheets("Additional Photos").Visible = xlSheetVisible
Worksheets("Additional Photos").Select Replace:=False
End If

Worksheets("_").Visible = xlSheetHidden

ActiveWindow.SelectedSheets.PrintOut

Worksheets("_").Visible = xlSheetVisible

Worksheets(Array("Cover Page", "Client Information", "Summary", "Additional Photos")).Visible = xlSheetHidden

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check Box to print in order

Post by HansV »

The problem is that depending on the values of the check boxes, some of the sheets in the array are already hidden. This causes the code to fail.
It's best to use If ... Then ... Endif for each of the check boxes to hide only the sheets that have been unhidden earlier on.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

Hi Hansv
I have a little understanding of what you are saying, but not much of a clue as to apply it
Refering to your last post

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check Box to print in order

Post by HansV »

Something like this:

Code: Select all

...

ActiveWindow.SelectedSheets.PrintOut

Worksheets("_").Visible = xlSheetVisible

If CheckBox1.Value = True Then
  Worksheets("Cover Page").Visible = xlSheetHidden
End If

If CheckBox2.Value = True Then
  Worksheets("Client Information").Visible = xlSheetHidden
End If

If CheckBox5.Value = True Then
  Worksheets("Summary").Visible = xlSheetHidden
End If

If CheckBox6.Value = True Then
  Worksheets("Additional Photos").Visible = xlSheetHidden
End If
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

Thank HansV
OOOOOH , I did not know that the check boxes can be used again within the same line of code, but I see how you applied it. I shall give it a try.

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

Hi HansV
I'm still getting an error message on line

Code: Select all

ActiveWindow.SelectedSheets.PrintOut
When I use hide sheet "_" before print using

Code: Select all

Worksheets("_").Visible = xlSheetHidden
But when I remove this line of code it prints this page along with other selections, except it seems to skip check box1 selection to print ? I know the spelling is correct.
Ckeck Box.txt
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check Box to print in order

Post by HansV »

What happens if you change False to True in the first Select statement only?

Code: Select all

If CheckBox1.Value = True Then
  Worksheets("Cover Page").Visible = xlSheetVisible
  Worksheets("Cover Page").Select Replace:=True
End If

If CheckBox2.Value = True Then
  Worksheets("Client Information").Visible = xlSheetVisible
  Worksheets("Client Information").Select Replace:=False
End If

If CheckBox5.Value = True Then
  Worksheets("Summary").Visible = xlSheetVisible
  Worksheets("Summary").Select Replace:=False
End If

If CheckBox6.Value = True Then
  Worksheets("Additional Photos").Visible = xlSheetVisible
  Worksheets("Additional Photos").Select Replace:=False
End If

Worksheets("_").Visible = xlSheetHidden

ActiveWindow.SelectedSheets.PrintOut

Worksheets("_").Visible = xlSheetVisible

If CheckBox1.Value = True Then
  Worksheets("Cover Page").Visible = xlSheetHidden
End If

If CheckBox2.Value = True Then
  Worksheets("Client Information").Visible = xlSheetHidden
End If

If CheckBox5.Value = True Then
  Worksheets("Summary").Visible = xlSheetHidden
End If

If CheckBox6.Value = True Then
  Worksheets("Additional Photos").Visible = xlSheetHidden
End If
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

Hi HansV
Ok I changed the first check box to True
I then selected check boxes 1, 2, 5, 6 and then pressed my print button
It seemed to go through the cycle OK, I then checked the print out and it seemed to have only printed check box 1 & 6.
I then checked to make sure all unhidden file have been re-hidden, except for “_” that all seemed to look good.
So I tried to print once more, using same check box selections (1, 2, 5, 6) , now when I selected print I developed a serious error and program wanted to close. The error line.

Code: Select all

Worksheets("Additional Photos").Visible = xlSheetVisible
They say patience is a virtue, I am defiantly learning the meaning of patience. lol
And I truely thank you for your help

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check Box to print in order

Post by HansV »

I'm afraid I have no idea why that line would cause a crash. Perhaps you should switch to Adobe Acrobat. It's expensive, but it handles multiple print commands in quick succession very well, I know from experience.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

I just tried to print using my actual printer just to see how that was going to respond
I also recieved the same errors.
Is the problem possibly the hidden sheets. If so I could modify the report to leave selected reports unhidden and the use a seperate command to print.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check Box to print in order

Post by HansV »

Again, without seeing the workbook I have no idea why the workbook crashes.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Check Box to print in order

Post by ABabeNChrist »

Here is a reduced version. I hope this is enought to get an idea
HomInspect 2010.zip
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check Box to print in order

Post by HansV »

I notice that several worksheets have Worksheet_Activate event procedures. It's probably better not to run those when the OK button is clicked. Try adding a line

Application.EnableEvents = False

at the beginning of CommandButton2_Click, after the MsgBox line. Also add a line

Application.EnableEvents = True

before the code that starts Word.
Best wishes,
Hans