Page Setup and Zoom Property

Someone might correct me but I think you are out of luck.

If you manually change the Fit to properties, Excel adjusts the figure in the zoom box (next time you look at that tab). But in VBA the Zoom property returns False if the FitToPagesWide or FitToPagesTall properties are set. So there is no way of finding out what the Zoom would be if they were unset.

That is what I was afraid you might say

Thanks!
CT

CT Witter said:

I have set all my pages to fit 1 x 1 and would now like to know the scaling % (Zoom) of the sheets.

Click to expand...

Maybe I don't understand the issue, but is this what you want?

Option Explicit
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()
'Lock the window so zoom cannot change during evaluation
LockWindowUpdate FindWindowA("XLMAIN", Application.Caption)
With ActiveSheet.PageSetup
.FitToPagesTall = 1
.FitToPagesWide = 1
.Zoom = False
End With
'Activate PrintPreview to evaluate current Zoom
SendKeys "%C"
ActiveSheet.PrintPreview
'Activate PageSetUp to return current Zoom
SendKeys "P%A~"
Application.Dialogs(xlDialogPageSetup).Show
MsgBox ActiveSheet.PageSetup.Zoom
LockWindowUpdate 0
End Sub

Exactly what I was looking for! Thanks Tom!!

Tom, if I needed to adjust the zoom from that point, that is after the zoom is determined by your formula, is that possible?

Like this:

ActiveSheet.PageSetup.Zoom = 90

which sets it to 90%.

Horse - -

Maybe Andrew's answer is what you are asking, and I could be wrong so apologies to all if I am, but based on what you wrote:
"if I needed to adjust the zoom from that point, that is after the zoom is determined by your formula..." then maybe you are asking about arriving at a zoom (for what reason I have no idea) based on whatever the zoom would be after the macro runs and determines what the original zoom is, which you wouldn't know as a reference point until you ran the macro.

Example, say you have several rows and columns populated that are wider and longer than 100% zoom if they are to be fit onto a 1x1 printed page. If you want to arrive at a zoom that is, say, 5% less than whatever that "base zoom" would be, then in the macro I posted, replace the line
MsgBox ActiveSheet.PageSetup.Zoom

with the lines:
Dim z As Integer
z = ActiveSheet.PageSetup.Zoom * 0.95
MsgBox z

or you can keep that first message box line in there just to see what the first "base zoom" is when you run the macro, and then the second message box will have that percentage reduced by 5% to give you the final answer to the question you might be asking.

I'd recommend sticking with percentages as multiplicands, not actual numbers as subtractors, because that "base zoom" might be smaller than what you want to subtract from it, and with numbers you'd run the risk of ending up with a negative zoom result, which is just too wierd for me to think about.

Thank you, Tom. Sorry for not being totally clear but yes, your answser was exactly what I was asking. Thanks!!

Very clever Mr Urtis.