VBA Different first page/odd page header/footer

tjdickinson

Board Regular
Joined
Jun 26, 2021
Messages
61
Office Version
  1. 365
Platform
  1. Windows
I'm working on a sheet that spans two pages, and I'm trying to format the page setup in VBA (as the culmination of a number of other operations the macro is executing). Since the result is on only two pages, it doesn't matter to me whether it's odd/even pages or different first page.

In my page setup block, I have the following code:
VBA Code:
With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .PaperSize = xlPaperA3
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .LeftMargin = Application.CentimetersToPoints(1.25)
    .RightMargin = Application.CentimetersToPoints(1.25)
    .TopMargin = Application.CentimetersToPoints(1.25)
    .BottomMargin = Application.CentimetersToPoints(1.25)
    .HeaderMargin = Application.CentimetersToPoints(0.5)
    .FooterMargin = Application.CentimetersToPoints(0.5)
    .CenterVertically = True    ' centre the sheet vertically on the page
    .CenterHorizontally = True    ' centre the sheet horizontally on the page
    .DifferentFirstPageHeaderFooter = True
    .FirstPage.LeftHeaderPicture.fileName = LogoFile
    .FirstPage.LeftHeaderPicture.Height = 55.5
    .FirstPage.LeftHeader = "&G"    ' inserts a picture (= LogoFile)
    .FirstPage.CenterHeader = "&""Alegreya Sans SC ExtraBold""&18&K354896" & shTitle    ' set the font, font size, and font colour with the sheet title
    .FirstPage.CenterFooter = "&""Alegreya""&9&K000000" & "Fold and attach here"
    .CenterHeader = "&""Alegreya""&9&K000000" & "Fold and attach here"
    .LeftFooter = "&""Alegreya""&9&K000000" & lFooter & "&A"    ' set the font, font size, font colour, and footer text with the sheet name
End With
However, when I run the macro, I get error 438 "Object doesn't support this property or method", and the debug points to the line .FirstPage.LeftHeaderPicture.fileName = LogoFile.

When I try the same thing but with .OddAndEvenPagesHeaderFooter, I get the same error message, and the debug points to .EvenPage.CenterHeader.

Without the different first page/odd and even pages option (and the respective properties removed), the headers/footers are correctly applied (to all pages); thus the issue doesn't seem to be with anything else in those lines, just with the .FirstPage/.EvenPage property.

I haven't been able to find anything online that gives more information about the .DifferentFirstPageHeaderFooter / .OddAndEvenPagesHeaderFooter properties, or about the .FirstPage / .EvenPage properties. Microsoft's documentation is no help at all except to verify that these properties exist.

Can anyone identify what's going wrong and how I need to fix it?

Thanks in advance for your help!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,643
With DifferentFirstPageHeaderFooter = True or OddAndEvenPagesHeaderFooter = True, use the LeftHeader property instead of LeftHeaderPicture:
VBA Code:
        .DifferentFirstPageHeaderFooter = True
        .FirstPage.LeftHeader.Picture.Filename = LogoFile
        .FirstPage.LeftHeader.Text = "&G"
        .FirstPage.CenterHeader.Text = "&""Alegreya Sans SC ExtraBold""&18&K354896" & shTitle    ' set the font, font size, and font colour with the sheet title
        .FirstPage.CenterFooter.Text = "&""Alegreya""&9&K000000" & "Fold and attach here"
There isn't an equivalent property for Height though.
 

Forum statistics

Threads
1,181,606
Messages
5,930,869
Members
436,764
Latest member
avalladarez

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
Top