Format footers in VBA

[mpromney]

Hi:


New member. Wondering if someone can help me solve a problem I'm having formatting footers in VBA. Here's is the relevant section of code I'm using:

With ActiveSheet.PageSetup
    .LeftFooter = "&""Calibri,Regular""&9 &D at &T"
    .CenterFooter = "&""Calibri,Regular""&9 CONFIDENTIAL - For Management Purposes Only"
    .RightFooter = "&""Calibri,Regular""&9 Page: &P"
End With

The worksheet I'm running this macro on is a balance sheet with the above footers already existing, except that they're in Times New Roman font. All I want to do is change the font from Times New Roman to Calibri. When I run the macro and print the results, the left and center footers are in Times New Roman and only the right footer is in Calibri. I must have run the macro 20+ times with all sorts of variations - all with the same frustrating result. What am I doing wrong? TIA for a response.

 

[Greg Truby]

Your code works fine for me. I ran the following against a new workbook, flipping the footers' formats back and forth several times. Each procedure yields the results you'd expect just by looking at the code.

Sub CaliFooters()
    
    With ActiveSheet.PageSetup
        .LeftFooter = "&""Calibri,Regular""&9 &D at &T"
        .CenterFooter = "&""Calibri,Regular""&9 CONFIDENTIAL - For Management Purposes Only"
        .RightFooter = "&""Calibri,Regular""&9 Page: &P"
    End With


End Sub


Sub TimelyFooters()


    With ActiveSheet.PageSetup
        .LeftFooter = "&""Times New Roman,Bold""&11 &D at &T"
        .CenterFooter = "&""Times New Roman,Bold Italic""&9 CONFIDENTIAL - For Management Purposes Only"
        .RightFooter = "&""Times New Roman,Italic""&14 Page: &P"
    End With


End Sub

Is there a chance there is more code that executes later that is resetting or undoing these lines of code?

EDIT - or is there a chance that the ActiveSheet isn't what you think it is?

 

[RoryA]

Welcome to the forum.

Are you by any chance using Application.PrintCommunication in your code?

 

[Greg Truby]

Are you by any chance using Application.PrintCommunication in your code?

Dagnabbit! That was the other point I was gonna mention and got distracted while writing the post forgot to do so!

Thanks, Rory!

 

[RoryA]

Dagnabbit!

Ahh, I've missed that.

 

[mpromney]

Thanks for the prompt responses, guys. Yessir! I am using Application.PrintCommunication. I assume that's not just a lucky guess on Rory's part. I should tell you that I don't know VBA. My macros have been constructed mostly by recording them and then cleaning them up as best I can. The code in question comes at the tail end of a macro that takes a balance sheet created by our accounting system and formats it in a format that people want to see. Here's the end of the macro:

'Set Page Layout settings
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$F$" & TotLiabsandCap + 1
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
    .LeftFooter = "&""Calibri,Regular""&9 &D at &T"
    .CenterFooter = "&""Calibri,Regular""&9 CONFIDENTIAL - For Management Purposes Only"
    .RightFooter = "&""Calibri,Regular""&9 Page: &P"
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
    .CenterHorizontally = True
    .CenterVertically = False
    .Orientation = xlPortrait
    .FitToPagesWide = 1
    .FitToPagesTall = False
    
    End With
    Application.PrintCommunication = True
    
End Sub

Your help in cleaning this up is much appreciated.

 

[Norie]

Try removing all the Application.PrintCommunication lines of code.

'Set Page Layout settings
    ActiveSheet.PageSetup.PrintArea = "$A$1:$F$" & TotLiabsandCap + 1
    
    With ActiveSheet.PageSetup
        .LeftFooter = "&""Calibri,Regular""&9 &D at &T"
        .CenterFooter = "&""Calibri,Regular""&9 CONFIDENTIAL - For Management Purposes Only"
        .RightFooter = "&""Calibri,Regular""&9 Page: &P"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With

 

[mpromney]

Many thanks, Norie. That seems to take care of the issue. Is Application.PrintCommunication problematic and something to avoid? Thanks again.

 

[mpromney]

Ugh! Now I have a different problem. The financial statements prints on 2 pages wide (8 cols on the first page and 2 cols on the next), even though the following code indicates to print on 1 page wide and ~ pages tall:

'Set Page Layout settings
    ActiveSheet.PageSetup.PrintArea = "$A$1:$J$" & NetInc + 1
    With ActiveSheet.PageSetup
        .LeftFooter = "&""Calibri,Regular""&9 &D at &T"
        .CenterFooter = "&""Calibri,Regular""&9 CONFIDENTIAL - For Management Purposes Only"
        .RightFooter = "&""Calibri,Regular""&9 Page: &P"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .FitToPagesWide = 1
        .FitToPagesTall = False
    
    End With

Any thoughts? I've tried everything I can think of. This part of the code worked before I removed the Application.PrintCommunication lines per above. TIA

 

[Greg Truby]

First of all, all due respect to Norie, I think yer gonna need that Application.PrintCommunication = True after your With ActiveSheet.PageSetup : ... : End With segment. In my experience, sometimes .PageSetup changes don't get "posted" until you set the print comms back to TRUE.

I would:

• Change my VIEW in Excel to Page Layout.
• Arrange my windows such that I can see both the main Excel app window and the VBE code window.
• Set a breakpoint at the beginning of the WITH statement and step through each line of code using the F8 key to see if the changes are even happening like I think they should.

I would also make sure I'm set to print to the printer I'm expecting to print to at the time this code is being called.