Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing font size in footer using a macro

    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

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Changing font size in footer using a macro

    .CenterFooter = "&8&" & ActiveWorkbook.FullName
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Changing font size in footer using a macro

    John,
    You don't actually want that second & in there:
    .CenterFooter = "&8" & MyFile
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing font size in footer using a macro

    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

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Changing font size in footer using a macro

    OK. I have been using it that way and it doesn't seem to mess things up, but I'll remove it.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing font size in footer using a macro

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

    Thanks again <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Changing font size in footer using a macro

    try something like the following :-
    <pre>.CenterFooter = "&8" + ActiveWorkbook.FullName </pre>

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

    Andrew C

  8. #8
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing font size in footer using a macro

    To not confuse anyone...John's second suggestion of

    CenterFooter = "&8" & MyFile

    works like a charm.

    Thanks! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Changing font size in footer using a macro

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing font size in footer using a macro

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

    Thanks for the help! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Changing font size in footer using a macro

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •