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

Posted on 2014-08-11
Last Modified: 2014-11-17
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.
Question by:eracer
    LVL 27

    Expert Comment

    by:Glenn Ray
    Attachment? :-)
    LVL 31

    Expert Comment

    by:Rob Henson
    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

    Author Comment

    I thought I attached it....

    Let's try again.
    LVL 20

    Accepted Solution

    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.

    Author Comment

    I'm having a tough time with this.  I imported the workbook1 macro, but it doesn't seem to do anything.
    LVL 20

    Expert Comment

    by:Ejgil Hedegaard
    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.

    Author Comment

    Thanks Ejgil,

    I will try that on Monday.

    Author Comment

    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.
    LVL 20

    Expert Comment

    by:Ejgil Hedegaard
    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.
    LVL 44

    Expert Comment

    by:Martin Liss
    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now