Excel打印与屏幕和分页符的条件内容/格式

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.

我在Excel中有一个VBA发布宏,它基于主配置工作表和许多带有数据的工作表生成已发布的工作簿。发布的版本是直接数据,仍然是格式化的,但没有公式,并且某些注释和背景信息被抑制。基本上,主配置包含要发布的工作表名称和范围的表。

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?

有没有办法做到这一点?

1 个解决方案

#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.

首先,插入一行并在分页符之前隐藏它。然后,您可以使用工作簿上的BeforePrint事件查找其中包含“查看下一页”文本的所有行,然后取消隐藏它们。

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:

这将使你有点需要去,但是,它会让你容易受到没有AfterPrint这一事实的影响。那么,你可以做的是以下几点:

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.

请注意,取消基本上会告诉您它是打印预览还是实际打印命令。我认为它非常好。

#1