PageSetup.Zoom=TRUE

[reklamo]

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]

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]

Hi Dave

Thanks so much for your prompt help. It's working great.

Regards
reklamo

 

[Peter]

I found the following Excel4 code at http://www.ozgrid.com/forum/showthread.php?t=49644&page=1

Andy Pope - OzMVP (Roobarb) - March 2003

that gets round having to do the PrintPreview, assuming that the zoom factor has been determined, but is just not accessible, as Zoom is off, and Fit to is on:

Use

Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{1,#N/A})"
Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})"

to switch zoom on, allowing you to pick up the factor.

If you have previously stored the Tall and Wide settings, you can then reinstate Zoom to false, and then Tall and Wide.

I've used this to rescale footers according to the degree of zoom.

Trust this helps.

Peter