Number multi-source documents easily with a comprehensive printing utility
By Stephen Nebel
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems
Microsoft Windows, Macintosh
Excel, Word, PowerPoint and other software programs offer powerful tools for creating financial reports and similar complex documents. But when all the pages for the complete document are stacked and nearly ready, a final task often remains— getting a consistent and continuous page number sequence across pages created from multiple sources. You can use the utility in this article to add page numbers to your multi-source documents programmatically.
To create a utility that prints page numbers for complex, multiple-source documents, we’ll:
• Provide an overview of how the utility works.
• Review the footer creation process with a manual walk-through.
• Design the utility’s UserForm and enter the proper code to automate the process.
If you take advantage of the Office suite and all its features, your reports and documents probably combine pages from several applications. And, the page numbering problem is often further compounded when you must include auditor statements, certificates of excellence, and similar third-party front material in the final document.
Think there’s no effective solution for this dilemma? Think again. With our solution, you’ll produce all pages for the document initially leaving the page number area blank. Once you’ve assembled the pages, you can then stack them in your printer tray in appropriate page groups. From there, our Excel utility handles the page numbering.
Peek inside the utility
We’re going to use a UserForm and some VBA code to craft our solution. We’ll allow users to select both start and end numbers, and print in Arabic or Roman numerals. We’ll provide a further option to print the continuation label often found accompanying page numbers in financial statements.
The utility prints numbers at the bottom center of each page in any of four formats. What’s more, you can easily modify the utility to accommodate other formats.
Users can choose the document’s start and end numbers, as well as alternation patterns for the continuation labels. Figure A shows the form itself, set to print page numbers I to V at the bottom of five pages.
This UserForm gives you several options for printing page numbers programmatically.
Page Number Footer Creation Exercise
Let’s start by looking at how to manually add a footer to a spreadsheet from the user interface.
Version differences: Note that these procedures are superficially different in the Excel 2007 interface. The VBA procedures and code will be identical, however. In fact, the utility will run unmodified in any version of Excel from 97 to 2007.
To walk through manual footer creation:
1. Create a new, blank workbook.
2. Choose View | Header And Footer from the menu bar.
3. Choose File | Page Setup from the menu bar to open the Page Setup dialog box.
4. Click on the Header/Footer tab if it’s not already selected, and then click the Custom Footer button.
5. Tab once to move to Center section, type (continued,) and then press Enter twice.
6. Click the Page Number icon PAGE NUMBER to add the page number. Click OK.
Figure B shows the Page Setup dialog box at this point in the process.
Add a custom footer to use with our printing utility.
If you look at Figure B, you’ll see that you need to be able to enter both text and control characters. The two small boxes in the Footer area represent the carriage returns created when we pressed Enter twice.
What may not be obvious from Figure B is that a stacked footer like the one above prints bottom-justified upwards from the footer margin setting. In short, we don’t need to raise or lower the footer margin to accommodate different heights in the footer material.
Create the utility
So our task is to simulate these footer settings programmatically with some additional automation thrown in. If you already have VBA programming experience, you should be able to knock this utility right out. But in case you’re not an expert, we’ll take things in simple stages, testing as we go.
To create a UserForm for our utility:
1. Create a new Excel workbook and name it PageNumberPrinter.xls.
2. Press [Alt][F11]. This key combination toggles us between the spreadsheet and the Visual Basic Editor (VBE).
3. Add a Userform to the project by right-clicking on PageNumberPrinter.xls (or on any of the objects belonging to it in the tree below), and then choosing Insert | UserForm from the resulting shortcut menu. The form should appear in the code window immediately to the right of the Project Explorer window.
To change the UserForm’s name:
1. Turn your attention to the UserForm’s properties window. (If it isn’t visible, choose View | Properties Window from the VBE’s menu bar.
2. Click the Alphabetic tab to bring (name) to the top of the list.
3. Type the name frm_Control in the field to the right. Note that the frm_ prefix will help us identify that we’re dealing with a Userform in code references.
Call the UserForm into action
A Userform can’t activate itself. It needs external code to call it into action. So let’s do that next.
To add code that calls the UserForm:
1. Right-click on PageNumberPrinter.xls or its tree below, and then choose Insert | Module from the VBE’s menu bar. This will insert a normal code module into the project.
2. The Properties Window will only show one property for the module, the (name) property. Rename the module PrintControl. Unlike the name frm_Control, which we are going to reference in our code, the name PrintControl just serves as a general reminder of the module’s purpose.
3. Enter lines 1 through 11 from Listing C to create our first procedure. Line 8 is the key line here. Show is a method of the Userform class. When this line executes, our form will pop up in the middle of the workbook screen.
Access the UserForm when you open the workbook
Lastly, the sole purpose of PageNumberPrinter.xls is to print page numbers, so we might as well have the workbook automatically show the form as soon as you open the workbook. Here’s how we do that.
To display the UserForm whenever you open the workbook:
1. Press [Alt][F11] to switch to the VBE. If the Microsoft Excel Objects portion of the tree for the project is closed, click the plus sign to expand it.
2. Double-click on ThisWorkbook. A special code module associated directly with the workbook itself opens. This is where s
