Need help in suppressing a page printout - SOLVED

[Rylandir]

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

 

[Chris Davison]

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]

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]

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]

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