PageSetup

[Simon Shaw]

Hi,

I am trying to set the Scaling in Page setup to an optimal zoom %.

I set the Fit to pages wide to say 1 then I want to check what the resulting
zoom to see if I should set it to 2 or more pages wide. But I am having
problems with the PageSetup.Zoom function:

With ActiveSheet.PageSetup
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
.PrintTitleColumns = False
End With
For x = 2 To 100
With ActiveSheet.PageSetup
If .Zoom < tbxMin_Zoom_Percent.Value Then
.FitToPagesWide = x - 1
x = 100
Else
.FitToPagesWide = x
End If
End With
Next x

Problem:
After setting the pages wide = 1, I want to check what Zoom % that Excel
calculated, then if it is smaller than the Text Box "tbxMin_Zoom_Percent",
then try setting the pages wide to 2, then check the zoom again until the
zoom % is higher than user's selected Minimum.

Problem is the code:
If .Zoom < tbxMin_Zoom_Percent.Value Then
doesn't work

thanks

Simon

 

[Vasant Nanavati]

There is not a really good way to do this, without using the dreaded
SendKeys.

Function FindZoom() As Integer
Dim oText As New DataObject
With Application
.ScreenUpdating = False
.SendKeys ("p%a^c~")
.Dialogs(xlDialogPageSetup).Show
.ScreenUpdating = True
End With
oText.GetFromClipboard
FindZoom = CInt(oText.GetText)
'MsgBox FindZoom
End Function

No error-trapping provided. Also, it's a bit slow, as are most VBA functions
involving PageSetup.

Use SendKeys at your own risk!

 

[Vasant Nanavati]

Sorry, the SendKeys string should be:

"p%a^c{ESC]"

 

[Dave Peterson]

Another version...

Jim Rech and Nick Osdale-Popa posted this that might help:

http://groups.google.com/groups?threadm=ejuo3kjsAHA.2344@tkmsftngp05

(It has some code to freeze the display, too.)