In the UI, you have some formatting options when entering a custom footer (File > Page Setup > Header/Footer). You also have those options when creating a footer in VBA, you just have to know the codes to use.
Special codes like page numbers and dates can be found in the constant definition section of the code below. It’s an ampersand followed by a letter (A for the sheet name, who came up with that?).
For fonts and font sizes, the format is
ampersand + font name in double quotes + ampersand + font size
which is all optional, that is, you only need to include the name or the size if you want to change them. I know exactly two font names from memory, so if you’re like me the easiest way to set this up is to do it in the UI and check the LeftFooter, CenterFooter, and/or RightFooter properties of the PageSetup object. Of course, all this applies to headers as well.
Here’s an example that creates a formatted footer and uses some (all?) of the special codes.
Dim sLeft As String
Dim sCenter As String
Dim sRight As String
Const sPAGE As String = “&P”
Const sPAGES As String = “&N”
Const sFILE As String = “&F”
Const sSHEET As String = “&A”
Const sDATE As String = “&D”
Const sTIME As String = “&T”
sLeft = sPAGE & ” of “ & sPAGES
sCenter = “&”“Albertus Medium,Bold”“&11This&”“Arial,Regular”“&10 is formatted text”
sRight = “[“ & sFILE & “]” & sSHEET & Chr$(10) & sDATE & ” “ & sTIME
With Sheet1.PageSetup
.LeftFooter = sLeft
.CenterFooter = sCenter
.RightFooter = sRight
End With
End Sub
There are a few other codes for alignment, formatting etc., and a full list on the “Formatting codes for Headers and Footers” Help page.
Be careful, though. Spot the bug in the following, which sets a 10pt footer:
Public Sub SetLeftFooter(ByVal sFooter As String)
ActiveSheet.PageSetup.LeftFooter = “&10? & sFooter
End Sub
If sFooter starts with a number, Excel thinks it’s part of the font size formatting, so you end up with a missing number and the rest of the footer in a very large font! The solution, of course, is to include space after the &10:
ActiveSheet.PageSetup.LeftFooter = “&10 ? & sFooter
You gave the answer before I read the question:) Cool, though, thanks for the pointing this out. I’ve never run into it before, but I can’t imagine how I would have debugged it if I did. That seems like a tricky one.
Formatting Codes for Headers and Footers
Excel Roll your own From Microsoft support: The following list contains the format codes that you can use in headers and footers.
How can I add a picture in the footer/header through Visual Basic?
First of all I’m not at all knowledgable about VBA code other than what I found on the internet today so please forgive me if I’m not explaining my question very well. I would like to format the date in the footer. If I write the following command &[Date] I get a date in the following format 4/23/2008. I would like the date to appear like this instead – 23APR08. Is this possible? Or am I asking too much?
Thanks in advance.
Is it possible to get the content of a cell in the footer?
Each sheet has a title. We would like to print this title in the footer.
Steve: You are able to get the contents of a cell in the footer. Here is an example of some code to take the contects of cell(1,1) and putting it in the footer:
Sub CreateFooters()
Sheet1.PageSetup.RightFooter = Sheet1.Cells(1, 1)
End Sub
Hi Jaime –
It’s quite doable. In Dick’s code, replace the line
Const sDATE As String = “&D”
With
Dim sDATE As String = Format(Date, “ddmmmyy”)
…Michael