Before you can print your Excel 2011 for Mac spreadsheet, you need to be familiar. Preview: Displays a preview of your document in the Mac OS X Preview. Every time her boss gives her one of those monster Microsoft Excel spreadsheets (the kind that span 10 pages across and have 20,000 rows of data) I can't say I blame her. Unless you've worked with Microsoft Excel a fair bit, the prospect of formatting something that large for printing is pretty daunting.
Last week, you saw my macro for adding worksheet data to the Excel footer, and formatting a date in the Excel footer. In that example, you had to run the macro by going to the View tab, and clicking the Macro command. To make the process easier, I decided to add event code to the workbook, so the macro would run automatically.
In the ideal Excel world, where all the worksheets are filled with unicorns and rainbows, that would be a simple task. But unfortunately, Excel 2010 is missing a rainbow or two. Keep reading, to see the Print Preview problem in Excel 2010, and my workaround.
Create the Event Code
To automate the footer macro, I wanted to use the Workbook_BeforePrint event. That would update the footer, before printing or previewing.
In the sample workbook, the macro to fix the footer is named SetFooter.
In the sample workbook, the macro to fix the footer is named SetFooter.
So, in the Visual Basic Editor, I opened the ThisWorkbook module, for the footer workbook. From the Object drop down list, I selected Workbook.
That creates a Workbook_Open event, but you can ignore or delete that.
In the Procedure drop down list, I selected BeforePrint
In the blank row in the BeforePrint procedure, I type the macro name – SetFooter
Now, the SetFooter macro should run when I print or preview the workbook.
Testing in Excel 2010
With rainbows still overhead, I changed the worksheet data, and clicked the Print Preview and Print icon on my Quick Access Toolbar (QAT).
![Print Print](/uploads/1/2/6/2/126247720/520472725.png)
Nothing changed! The preview footer still showed the old information, so the SetFooter macro hadn't run.
In previous versions of Excel, the same code worked perfectly, when clicking the Preview button. I tested in Excel 2007 and Excel 2003, just to be sure.
Excel 2010 Preview Workaround
![For For](/uploads/1/2/6/2/126247720/845212089.png)
In previous versions of Excel, the same code worked perfectly, when clicking the Preview button. I tested in Excel 2007 and Excel 2003, just to be sure.
What's different in Excel 2010? Now, instead of a Preview window, the workbook switches to the File tab when you click Preview, and shows the Print settings.
After some searching, I found an old Preview command, and added that to the QAT. Here's how to do that:
- On the QAT, click the Customize arrow, at the far right.
- Click More Commands, to open the Excel Options window.
- From the Choose Commands From drop down, select Commands Not in the Ribbon
- Scroll down the list, and click on Print Preview Full Screen
- Click the Add button, to put the command on the QAT
- Click OK, to close the Excel Options window.
Now, you can click the Print Preview Full Screen icon on the QAT, and the BeforePrint event is triggered, to run the SetFooter macro.
Be careful though – the Print Preview Full Screen icon looks exactly like the Print Preview and Print icon.
___________
___________