XL97:Suppressing Print Preview

[Nick Osdale-Popa]

I am manually setting pagebreaks in a worksheet and I want to make sure that
the sheet prints to 1 page wide. I have to use the PrintPreview method to
retrieve the Zoom factor so that the pagebreaks will work. I use the
following code to accomplish this. (if there is a better way please let me
know!)

Even with ScreenUpdating set to False Excel still "flashes" the
PrintPreview. Is there another way of suppressing this?

Dim ZoomFactor as Integer
With Activesheet.PageSetup
.FitToPagesTall = False
.FitToPagesWide = 1
End With
'in order to calculate the Zoom %, a PrintPreview must initiated.
SendKeys "%C"
ActiveSheet.PrintPreview
'to get/set the Zoom %, initiate the Page Setup Dialog box.
SendKeys "P%A~"
Application.Dialogs(xlDialogPageSetup).Show
ZoomFactor = ActiveSheet.PageSetup.Zoom
ActiveSheet.PageSetup.Zoom = ZoomFactor

[Jim Rech]

Good work figuring out how to get the zoom. I've added some additions that
should suppress the screen:

Declare Function LockWindowUpdate Lib _
"user32" (ByVal hwndLock As Long) As Long
Declare Function FindWindowA Lib _
"user32" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Sub SetPageZoom()
Dim ZoomFactor As Integer
Dim hWnd As Long

hWnd = FindWindowA("XLMAIN", Application.Caption)
LockWindowUpdate hWnd

With ActiveSheet.PageSetup

.FitToPagesTall = False
.FitToPagesWide = 1

.Zoom = False

End With
'in order to calculate the Zoom %, a PrintPreview must initiated.
SendKeys "%C"
ActiveSheet.PrintPreview
'to get/set the Zoom %, initiate the Page Setup Dialog box.
SendKeys "P%A~"
Application.Dialogs(xlDialogPageSetup).Show
ZoomFactor = ActiveSheet.PageSetup.Zoom
ActiveSheet.PageSetup.Zoom = ZoomFactor

LockWindowUpdate 0
End Sub

--
Jim Rech
Excel MVP