Excel 2010 Print Preview Problems

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.

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

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

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.

Add Command to QAT

After some searching, I found an old Preview command, and added that to the QAT. Here’s how to do that:

1. On the QAT, click the Customize arrow, at the far right.
2. Click More Commands, to open the Excel Options window.
3. From the Choose Commands From drop down, select Commands Not in the Ribbon
4. Scroll down the list, and click on Print Preview Full Screen
5. Click the Add button, to put the command on the QAT
6. 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.
___________