Page Setup Dialog Box

Page tab

With ActiveSheet.PageSetup 
    .Orientation = xlPageOrientation.xlLandscape
    .Zoom = 100
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .FitToPagesTall = False ' Automatic
    .PaperSize = xlPaperSize.xlPaperA4
    .PrintQuality = 300
    .FirstPageNumber = Constants.xlAutomatic | 5
End With

Margins tab

Margins are set or returned in Points.
The units that are displayed on the Page Setup dialog box are inches although the units accepted for the following properties are Points.
How can you define 0.5 (or the smallest margins) for all margins ??

With ActiveSheet.PageSetup 
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .CenterHorizontally = False
    .CenterVertically = False
End With

You can use the following conversion functions to help convert between the various units.

ActiveSheet.PageSetup.LeftMargin = Application.CentimetersToPoints(1.9) 
ActiveSheet.PageSetup.LeftMargin = Application.MillimetersToPoints(1.9)
ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(1.9)

For more details, please refer to the Measurements page.

Header and Footer tab

This is from the Header/Footer tab of the Page Setup dialog box.

With ActiveSheet.PageSetup 
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
End With

In Excel 2002 you can include the full file path in a custom header or footer

ActiveSheet.Pagesetup.CenterFooter = ActiveWorkbook.FullName 
Worksheets(1).PageSetup.CenterFooter = ActiveWorkbook.FullName

It is possible to write code to insert a page header in the centre position although the CenterHeader property (as well as the other Header and Footer properties) are not very helpful.
These should be objects but do not appear to be ??

ActiveSheet.PageSetup.CenterHeader = &""Arial,Bold Italic""&11Better Solutions" 

Sheet tab

With ActiveSheet.PageSetup 
    .PrintArea = "$B$2:$D$20"
'defining the print area automatically displays page break lines on the worksheet
'these can be hidden using the following line: ActiveSheet.DisplayAutomaticPageBreaks = False

    .PrintTitleRows = "$B$2:$D$20"
    .PrintTitleColumns = ""
    .PrintGridlines = False
    .BlackAndWhite = False
    .Draft = False
    .PrintHeadings = False
    .PrintComments = xlPrintLocation.xlPrintNoComments
    .PrintErrors = xlPrintErrors.xlPrintErrorsDisplayed
    .Order = xlOrder.xlDownThenOver
End With

Chart tab

Removed in 2007
Only available if you currently have a chart selected / active

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext