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

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





"Simon Shaw" <simonATsimonstoolsDOTcom wrote in message
...
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

Sorry, the SendKeys string should be:

"p%a^c{ESC]"

--

Vasant


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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





"Simon Shaw" <simonATsimonstoolsDOTcom wrote in message
...
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

Another version...

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

http://groups.google.com/groups?thre...%40tkmsftngp05

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


Simon Shaw wrote:

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

--

Dave Peterson