MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to format footer/header

Posted by Gideon on October 27, 2000 5:27 AM

I want to create a macro that I'll assign to a toobar button. It will look something like

Sub UpdateFooter()
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
End Sub

I want to change the font to 8 point size or somewhere there. How can I add that feature? I tried working according to the help file by adding "&08", but the macro then gives an error. Help!

Posted by Ivan Moala on October 27, 2000 5:55 AM

Here is an example of one I have used;
Just take what you need
Sub SetPrt()
Dim Lft As String, Ctr As String, Rght As String
Lft = ActiveSheet.Range("J1")'Left Text to print
Ctr = ActiveSheet.Range("K1")'Centre text to print
Rght = ActiveSheet.Range("L1")'Right Txt to Prt

With ActiveSheet.PageSetup
.LeftHeader = "&l" & "&""Arial Black,Bold""&12" & Lft 'ActiveSheet.Range("J1") 'change J1 to your cell input
.CenterHeader = "&""Arial Black,Bold""&8 " & Ctr 'ActiveSheet.Range("K1") 'change K1 to your cell input
.RightHeader = "&""Times New Roman,Bold""&16 " & Rght ' ActiveSheet.Range("L1") 'change L1 to your cell input
End With
End Sub