Need help in suppressing a page printout - SOLVED

Rylandir

New Member
Joined
Feb 18, 2002
Messages
24
Hi all,

I need help with this one. I'm fairly sure that it's going to require some VBA coding (perhaps in the BeforePrint event?), but am at a loss as to what to speficially code.

I have a "sales proposal" workbook with multiple worksheets for various products which is used by a salesperson to produce a printed proposal.

As a salesperson enters/selects data in these varying worksheets, a "printable" worksheet within this workbook which has been previously formatted to print in 4 pages (basically 1 page for each product and a final Summary/Totals page) is filled in with the appropriate data. When done, the salesperson then selects this "printable" worksheet and prints it.

For instance,

Worksheet A is the software products page.
Worksheet B is the hardware products page.
Worksheet C is the Training/Services page.
Worksheet D is the "printable" page which has been formatted to print in 4 pages.

Here's what I need to do ...

When printing this "printable worksheet", I need it to suppress printing pages for which no product has been selected.

In other words, if a salesperson selected products in the software worksheet only, then when printing the "printable" worksheet, it should only print page 1 and page 4 (which is the software portion and the Totals/Summary portion).

Using the above example, I thought of writing code in the BeforePrint event to hide the rows that comprised pages 2 and 3 in the "printable" worksheet.

However, I then found that there is no AfterPrint event, thus I could not UNHIDE the rows after the printout was done.

Does anyone have any other idea/solution for how to suppress a page from printing?
This message was edited by Rylandir on 2002-08-29 14:45
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
I think I do something similar..... could you apply filtering to hide the rows you don't want to print ?

I mean, filter via VBA...

I have a sheet with maybe 3000 rows, where one of the columns contains a code.... I print out sheets according to code, just by autofiltering in the VBA, so it prints all the code 1s, then all the code 2s then all the code 3s etc etc

might that help ?

Chris
 

Rylandir

New Member
Joined
Feb 18, 2002
Messages
24
Chris, thanks for the reply.

Filtering via VBA might help ... but how do you "unfilter" after the printout? Wouldn't you run into the same issue I did with how to "unhide" rows automatically via VBA after the worksheet is printed?

Considering that the end users of this workbook are not very computer literate, I basically need to automate the process through code without requiring user input/interaction.
 

Rylandir

New Member
Joined
Feb 18, 2002
Messages
24
Figured it out ... surprisingly easy now that I look at it. :b

I basically just added this code into the BeforePrint event.


If Sheet8.Range("L110").Value = "YES" Then
'Print only page 1, 3, 4
Sheet8.PageSetup.PrintArea = "$A$1:$J$55,$A$112:$J$167,$A$168:$J$223"
Else
'Print all 4 pages
Sheet8.PageSetup.PrintArea = "$A$1:$J$223"
End If

Cell L110 is set to YES to suppress page 2 when needed.
This message was edited by Rylandir on 2002-08-29 14:46
 

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
Nice one.... from memory, I used something along the lines of


filter on all items called "John" :

Selection.AutoFilter Field:=1,Criteria1:="John"

now print them :

ActiveWindow.SelectedSheets.PrintOut
Copies:=1

now unfilter back to all items

Selection.AutoFilter Field:=1

ready to move onto the next criteria to print

:)
 

Forum statistics

Threads
1,148,051
Messages
5,744,520
Members
423,881
Latest member
Nguyen Vu

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