Changing font size in footer using a macro

Topic

New Reply

Sub UpdateFooter()
MyFile = ActiveWorkbook.FullName
ActiveSheet.PageSetup.CenterFooter = _
MyFile

With ActiveSheet.PageSetup
.LeftFooter = “&8&D&T”
.CenterFooter = “&8&C:My DocumentsInvestments”
.RightFooter = “&8&A”
End With
End Sub

I have this macro which automatically inserts the file path into the footer on a spreadsheet. The default font size in Excel is 10pt. I would like the footer to be 8pt. I’m able to change the font size on the left and right footers but can’t figure out how to change the macro so that it will change the font size in the center footer while still going out and looking for the path of the particular spreadsheet. In my example above the path is ‘hard coded’ in.

Any suggestions?

Thanks in advance.

Christa

Reply | Quote

Replies

.CenterFooter = “&8&” & ActiveWorkbook.FullName

Reply | Quote

John,
You don’t actually want that second & in there:
.CenterFooter = “&8” & MyFile

Reply | Quote

OK. I have been using it that way and it doesn’t seem to mess things up, but I’ll remove it.

Reply | Quote

John,
I should have been more specific – Excel has certain formatting codes built-in for headers and footers:
&L Left aligns the characters that follow.
&C Centers the characters that follow.
&R Right aligns the characters that follow.
&E Turns double-underline printing on or off.
&X Turns superscript printing on or off.
&Y Turns subscript printing on or off.
&B Turns bold printing on or off.
&I Turns italic printing on or off.
&U Turns underline printing on or off.
&S Turns strikethrough printing on or off.
&D Prints the current date.
&T Prints the current time.
&F Prints the name of the document.
&A Prints the name of the workbook tab.
&P Prints the page number.
&P+number Prints the page number plus the specified number.
&P-number Prints the page number minus the specified number.
&& Prints a single ampersand.
& “fontname” Prints the characters that follow in the specified font. Be sure to include the double quotation marks.
&nn Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.
&N Prints the total number of pages in the document.

Therefore having that second ampersand there will have varying effects, dependent on which drive your file is saved on (e.g. Drive C, you’re effectively using &8&C: which translates to 8 point font, centred, text is “:”
FWIW.

Reply | Quote

In fact that extra “&” WAS munging something up! Since most of my work is saved on “U:”, that line was underlined and the “U” wasn’t printing out.

Reply | Quote

Oops …thanks, you answered before I asked. Your second suggestion worked.

Thanks again

Reply | Quote

Sorry, Rory…It’s really early in the morning here…You get the credit. Your suggestion works great!

Thanks for the help!

Reply | Quote

Sorry, John…I tried that and it doesn’t work…the center footer is still 10pt. Do I need to change something else in the macro along with that line?

Thanks,

Christa

Reply | Quote

try something like the following :-

.CenterFooter = "&8" + ActiveWorkbook.FullName 

This will of course cause an error if the file is not saved.

Andrew C

Reply | Quote

To not confuse anyone…John’s second suggestion of

CenterFooter = “&8” & MyFile

works like a charm.

Thanks!

Reply | Quote