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.

beforeprintproblem01

So, in the Visual Basic Editor, I opened the ThisWorkbook module, for the footer workbook. From the Object drop down list, I selected Workbook.

beforeprintproblem02

That creates a Workbook_Open event, but you can ignore or delete that.

In the Procedure drop down list, I selected BeforePrint

beforeprintproblem03

In the blank row in the BeforePrint procedure, I type the macro name – SetFooter

beforeprintproblem04

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

beforeprintproblem05

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.

beforeprintproblem06

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.

beforeprintproblem07

Now, you can click the Print Preview Full Screen icon on the QAT, and the BeforePrint event is triggered, to run the SetFooter macro.

beforeprintproblem08

Be careful though – the Print Preview Full Screen icon looks exactly like the Print Preview and Print icon.

___________

You may also like...

3 Responses

  1. AlexJ says:

    Brilliant, Debra! Ive been looking for the Print Preview Full Screen functionality since I started using ’10 – I’m not enjoying the more mainstream print preview functions.

  2. Stewart Mills says:

    Hi Debra

    If more people spent time giving explanations as comprehensive and easy to understand as you just did I would be thrilled.

    I find this publication to be a top notch explanation to a problem; thanks very much.

    Stu Mills

  3. No Zoom says:

    In Excel 2010 the zoom button is disabled in Print Preview Full Screen, is there a way to enable this?

    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *