• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Excel - this is a tough one... conditional footers.

I've attached a redacted copy of a macro-enabled file.  One of the macros is event-based: 'Insert row with double-click.'

The problem is that as rows are added, Excel pushes cells G27:M38 down the page, splitting the range of cells and pushing some of it to the next page.  I want that range to ONLY appear on the last page.

Example:  If there are 1-9 line item rows, then there will be room on the first page for the 'TERMS / Signature' cells to be a 'footer' on the last page.  But if the salesperson adds more than 9 line item rows, then the TERMS section rows start getting split up.

What I want is for the TERMS section to be at the bottom of the LAST page ONLY.  So if the sheet has more than 9 'Line Items' on it, the entire TERMS section gets pushed to the bottom of the second page.  And if more line items are added, such that page 2 has more than 9 line items, the TERMS section gets pushed to the bottom of page 3, etc.

It's like a conditional footer - a footer that only appears on the last page.

On a related note, I can't figure out how to even create a footer (not that I need to in this case) on this sheet, since it has 'Freeze Panes' turned on.

Disclaimer - the macros in this workbook were created by others.  I don't know VBA, and am very grateful for the help I've gotten here with this workbook.
1 Solution
Glenn RayExcel VBA DeveloperCommented:
Attachment? :-)
Rob HensonIT & Database AssistantCommented:
Without an attachment, thinking outside the box.

When doing a print, you could have a routine check the number of rows of "Data" before the "Terms" section and if required insert a page break as necessary.

There is a Before_Print worksheet event that can trigger the routine but don't know if it can insert a page break at an approriate time to change the printed document; ie will the "Print File" have already gathered its necessary data which may or not require a page break inserting?

Rob H
eracerAuthor Commented:
I thought I attached it....

Let's try again.
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Ejgil HedegaardCommented:
Try this.
I have named the range with the terms (name=Terms), and added a Before Print macro in the workbook module.
The macro is setting a manual page break just above the terms, if a page break exist somewhere in the Terms area, due to the number of Item lines.
The page breaks are reset in the 2 other subs, inserting or deleting rows.

It does not do exactly what you have stated in the question (9 lines on each page), because when the terms are moved to the next page, there are space for more lines on the page.
eracerAuthor Commented:
I'm having a tough time with this.  I imported the workbook1 macro, but it doesn't seem to do anything.
Ejgil HedegaardCommented:
The macro must be in the module ThisWorkbook.
It runs before print or print preview, and checks if there is a page break in the Terms area, named Terms.
Then a manual page break is set just above the terms, and the terms will be pushed to page 2.
So nothing happens until print, print preview or save as pdf (equal to print) is used.
Add some extra lines, to have more than 8, and then press print preview.

Observe the page break reset line "Sheet1.Cells.PageBreak = xlPageBreakNone" in the other subs, DeleteMe in Module1 and BeforeDoubleclick in Sheet1 module.
eracerAuthor Commented:
Thanks Ejgil,

I will try that on Monday.
eracerAuthor Commented:
I exported the .bas and .cls files from the Experts-Clean template, then tried to import them into my template.  Nothing happened.  So I opened both templates and copy/pasted the text I found in ThisWorkbook, Sheet1, and Module1.

The macro is keeping the 'Terms section whole as I add rows.  Excellent!  But now when I open my template I get an error message when I run 'Print Preview'  (Run-time error '424': Object Required)  Debug points to:

Set rg = [Terms]

I notice that 'ThisWorkbook changes from ['Workbook' - 'BeforePrint'] to ['General' - Declarations''] when I reopen the saved template.  In the template Ejgil provided, ThisWorkbook shows as ['Workbook' - 'BeforePrint'].  It doesn't save that way when I set it in my template.
Ejgil HedegaardCommented:
The run time error is because the named range Terms is not defined.
You must have missed that in the transfer process.
The named range Terms are the range column G to M, starting with TERMS: in column G, and ending in the row with "Experience Empowering Excellence".
The range is named to keep the reference when the cells are moved when rows are inserted or deleted.
The easiest way to name a range, is to mark (select) the range, then select the box just above column A, left to the formula line, type Terms and Enter.

When you get the error, the macro Workbook_BeforePrint is running.
With the cursor positioned above (outside) the macro, the definition is General-Declaration.
Set the cursor in the code area of the macro Workbook_BeforePrint, and you will see that the definition change to Workbook-BeforePrint.
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now