PrintPreview in 2007 (for what it's worth...)

[rpw]

Hi all,

I just figured something out and wanted to share this with you.

Maybe it is common knowledge that your macros will run quicker if you turn
off screen updating (Application.ScreenUpdating = False), I don't know, but
be careful how/when you use it in conjunction with the PrintPreview method in
Excel 2007.

I have an application that runs several row height changes and row hiding
events in loops on a hidden sheet so I had the screen updating turned off
until the macro was finished running. Near the end of the macro I use the
PrintPreview method to reveal the hidden sheet so the user can view what the
end product looks like and then print it if so desired (without having to
unhide the protected sheet). This worked fine in XP / 2003. However, in
2007 there was no way for the user to print from this preview.

I tinkered with the code for a while and ending up discovering that if
screen updating is set to 'True' just prior to the PrintPreview method and
set back to 'False' immediately afterwards then I would get a normal print
preview where the user could access the print command. Unfortunately in 2007
there is a flash of the hidden sheet just prior to the preview and again just
after the preview. The hidden sheet was never seen (except in preview) in
2003/XP. No very slick looking now...

If you have 2007, you might paste the two simple samples below into a module
to see the difference I'm describing. You will want to setup a couple of
command button to trigger the macros and also make sure there is at least one
entry in the cells of Sheet1 so there is something to print.

Sub PrintPreviewPrint()
Worksheets("Sheet1").PrintPreview
End Sub

Sub PrintPreviewOnly()
Application.ScreenUpdating = False
Worksheets("Sheet1").PrintPreview
Application.ScreenUpdating = True
End Sub