Check Box to print in order
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Check Box to print in order
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
Don
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
Hi Don
How do I go about achieving this? And will this work with hidden sheets also.
I tried using
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.
How do I go about achieving this? And will this work with hidden sheets also.
I tried using
Code: Select all
ActiveWindow.SelectedSheets.PrintOut
Any helpful suggestion is greatly appreciated, I have been running around in circles for days now trying to get it to function smoothly.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check Box to print in order
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
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
When I run the code I get an error on the code that hides all open sheets.
I also tried ActiveWindow, ActiveWorkbook, WorkSheets and sheet
Code: Select all
ActiveSheet("Cover Page", "Client Information", "Summary", "Additional Photos").Visible = xlSheetHidden
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check Box to print in order
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
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
I'm still getting an error on same line
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check Box to print in order
At least one sheet must remain visible. If you try to hide all sheets, you'll get an error message.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
Here is the code I was using
I Hide the activesheet "_" before print and then unhide after
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
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check Box to print in order
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.
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
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
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
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check Box to print in order
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
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.
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.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
Hi HansV
I'm still getting an error message on line
When I use hide sheet "_" before print using
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.
I'm still getting an error message on line
Code: Select all
ActiveWindow.SelectedSheets.PrintOut
Code: Select all
Worksheets("_").Visible = xlSheetHidden
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check Box to print in order
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
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.
They say patience is a virtue, I am defiantly learning the meaning of patience. lol
And I truely thank you for your help
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
And I truely thank you for your help
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check Box to print in order
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
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.
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.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check Box to print in order
Again, without seeing the workbook I have no idea why the workbook crashes.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Check Box to print in order
Here is a reduced version. I hope this is enought to get an idea
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check Box to print in order
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.
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
Hans