PrintPreview problem in 2007

[Corey ....]

I have some rather lengthy code that populates data into a worksheet from
the users inputed data in a form.

Once a button is pressed, the data is taken from the form an placed into the
worksheet.

Then (in Excel 2003) the sheet is viewed in PrintPreview mode for viewing.
The user can then click the Print Button on the menu and then click Close
from the menu.
The rest of the code then clears the data from the worksheet and saves the
data into another worksheet for storing.

The problem is, one user is using Excel 2007, and the when the code executes
the user gets the worksheet in the preview, but the Ribbon does NOT change
to PrintPreview Mode,
and thus allowing/enabling the Print and Close Buttons fromt eh ribbon.
The standard Ribbon remains, with no way to Close the Preview except to
Close the file from the Taskbar, which simply closes the Preview view, but
also cancels the rest of the code.

Funny thing is IF i create a dumby workbook (Book1) and run ONLY the section
of code that Previews the Sheet, the Ribbon changes to the Printpreview
Mode.
Thus enabling the Print and Close buttons.

The section of code that works OK by itself is:

' Other code above here generally
==========================
With ActiveSheet.PageSetup
..Orientation = xlPortrait
..BlackAndWhite = True
..PrintGridlines = False
..Draft = False
..PrintQuality = -1
ActiveWindow.SelectedSheets.PrintPreview
End With
==========================
' Other code below here too


Is there a way to make 2007 change to the PrintPreview mode ?

Corey....

 

[Rob Bovey]

Hi Corey,

There may be another way around this, hopefully someone else will chime
in. I've seen this problem as well and with my very limited investigation of
it there appear to be only two options:

1) Make sure the PrintPreview command is the last thing in your procedure.
Any other non-trivial code run after a PrintPreview command seems to cause
Excel 2007 to replace the print preview ribbon with the normal Excel UI
ribbon and everything is then locked up as you have discovered. The (really
unacceptable) workaround to this is....

2) Teach your users to press the ESC key to get out of the Excel 2007 print
preview mode. This seems to work regardless of the state of the Ribbon, but
it's a piss poor solution.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm