Saved from a previous post...

Jim Rech collaborated with Nick Osdale-Popa to create this interesting code that
uses .printpreview.


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 'see note below

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 'see note below
End Sub

The lockwindowupdate will suppress any screen flicker. But if something goes
wrong, it's reboot time. I wouldn't use them. I'd live with minor flashing.

reklamo wrote:

I have a worksheet with e.g. 25 pages. I want to set the Zoom value that e.g.
4 of the original pages fit to one new page via VBA.
For this I select 4 original pages, define this range as PrintArea and set
the Pagesetup to Fit to 1 page wide by 1 page tall with FitToPagesWide and
FitToPagesTall. Before I have to set .Zoom to FALSE.
With this setting the Zoom value is set to e.g. 34%. Then I want to set
Zoom=True and select the whole sheet as PrintArea.
Question: How can I read out the Zoom value als long as Zoom=False, or how
can I set Zoom=True without setting a specific Zoom value?
Thanks in advance for all helps.

Regards
reklamo

--

Dave Peterson