The following sub is supposed to prepare pagesetup for a PDF output. For instance, if due to other attached printers the pagebrakes are messed up, the sub should fix it back to 1 page wide and 3 pages tall.
Sub adjustPB(ws As Variant, ps As XlPaperSize)
'On Error Resume Next
Application.DisplayAlerts = False
Application.PrintCommunication = False
With ws.PageSetup
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.Orientation = xlLandscape
'.Orientation = xlPortrait
.PaperSize = ps
.Zoom = 100
.Zoom = False
Debug.Print .Zoom
.FitToPagesWide = 1
Debug.Print .Zoom
Debug.Print .FitToPagesWide
.FitToPagesTall = 3
End With
Application.DisplayAlerts = True
Application.PrintCommunication = True
End Sub
The sub actually works as expected in single step (F8), when I add a breakpoint at 'With ws.PateSetup'. If I run it using F5, however, it ignores the statements. The debug prints show, that the values for the properties didn't change.
Things tried so far: add delays before .zoom and .FitPagesWide with DoEvents for up to 1 sec. No change. Zoom for instance remains 55. In singlestep, Zoom reads FALSE in the end. Any explanations / hints what's going wrong here?
.PrintCommunication
may be the key. The documentation is rather obscure at this point but it looks like Excel caches all commands when .PrintCommunication
is OFF and dumps them to page setup engine when you turn .PrintCommunication
ON. That may be the reason for not seeing any changes when running with F5. (The services of the debugger are even more obscure for me.)
Try applying
With ActiveSheet.PageSetup
' ....
.Parent.Application.PrintCommunication = True
.Zoom = 100
.Zoom = False
Debug.Print .Zoom
.FitToPagesWide = 1
Debug.Print .Zoom
Debug.Print .FitToPagesWide
.FitToPagesTall = 3
.Parent.Application.PrintCommunication = False
' ....
End With
Application.PrintCommunication = True
I'am also curious to see the result :)