Retriving print zoom value

bedasy

New Member
Joined
Sep 14, 2016
Messages
1
I'm using Excel 2016 with Microsoft office 365. I need to retreive the pagesetup.zoom value for a area fitted into one page wide by one page tall.

Some Introduction

Before printing a worksheet, I want to run a page setup macro that will adjust the print zoom value to make sure the printed page look the same no matter on wich computer or printer you run it and all page break are at the same place. On the worksheet, all my rows and colums are of the same hight and same width and canot be modified. Depanding on the printer's marging, somes times I ended up with the last row or column of the page on the next page.

I solve the problem by creating a macro that select on the worksheet the area of the first page too print and fit the print area to 1 page tall by one page wide. What I need to do at that point if to retreive the zoom value for that fitting. But at that point the pagesetup.zoom value return false since it is set to fit one page wide by one page tall.

If you do it manually in Excel and go back to the page setup wizard after tyou fitted the area of the first page, you can see the zoom value is there (but cannot be edited). By selecting the zoom print option, it retain the value and then you can reset the print area to the full worksheet. The following macro worked fine on many computer with different version of excel and different printer and my goal was achived. But I'm now working in a new office (new computer, new printer) and now the macro is not working. The only difference I can think of is office 360 vs 365. the pagesetup.zoom value get reset to 100% at some point.

Any Ideas?

Sub MiseEnPage()
Me.Select
'Me is a worksheet

Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False

With Me.PageSetup
.Zoom = 100
.PrintTitleRows = ""
.PrintArea = "$A$1:$AO$52"
End With

Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False

With Me.PageSetup
.PaperSize = 1
.Orientation = xlPortrait
.LeftMargin = Application.CentimetersToPoints(0.4)
.RightMargin = Application.CentimetersToPoints(0.4)
.TopMargin = Application.CentimetersToPoints(0.6)
.BottomMargin = Application.CentimetersToPoints(1.6)
.HeaderMargin = Application.CentimetersToPoints(0)
.FooterMargin = Application.CentimetersToPoints(0.7)
.PrintGridlines = False
.CenterHorizontally = True
.CenterVertically = False
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False

At this point pagesetup.zoom=false

With Me.PageSetup
.FitToPagesWide = False
.FitToPagesTall = False
End With

Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False

At this point, before office 365 the pagesetup.zoom value was preserved to the fitted page (Ex: pagesetup.zoom=97%).
Now the value is reset to 100% at this point


With Me.PageSetup
.PrintTitleRows = "$1:$9"
.PrintArea = "$A:$AO"
End With

Application.PrintCommunication = True
DoEvents

End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top