Excel Conditional Content/Formatting for Print vs. Screen and Page Breaks

Question

I have a VBA publishing macro in Excel which generates a published workbook based on a master configuration worksheet and a number of worksheets with the data. The published version is straight data, still formatted, but with no formulas and with certain notes and background information suppressed. Basically, the master configuration contains a table of worksheet names and ranges to be published.

To make the published version printable, some of the worksheets need page breaks inserted. I realize I can insert these based on the same configuration, and I can also change the print orientation and margins of a particular worksheet, by adding a few more columns to my master sheet.

Here comes the tricky bit. If a table has some of these page breaks inserted during the publishing process, I would like an informational row to be inserted before the page break indicating that the table is continued on the next page. But I only want this to show up in the printed version, not displayed in the on-screen display.

Is there a way to do this?

Answer 1

First, insert a row and hide it just before the page break. Then, you can use the BeforePrint event on the workbook to find all rows with the "See Next Page" text in it, and then unhide them.

Sub Workbook_BeforePrint(cancel as Boolean)
    Dim rngCell as Range
    set rngCell = ActiveSheet.UsedRange.Find("See Next Page")

    while not rngCell is Nothing
        if not rngCell is Nothing then
            rngCell.EntireRow.Hidden = false
        end if
        set rngCell = ActiveSheet.UsedRange.FindNext()
    loop
End Sub

This will get you somewhat were you need to go, however, it will leave you susceptible to the issue of the fact that there's no AfterPrint. So, what you can do is the following:

Sub Workbook_BeforePrint(cancel as Boolean)
    Application.EnableEvents = false

    'Unhide rows here

    if cancel then
        Workbook.PrintPreview()
    else
        Workbook.PrintOut()
    end if

    'Rehide rows here

    Application.EnableEvents = True
End Sub

Note that cancel will essentially tell you if its a print preview or an actual print command. Its very kind of it, I think.