Results 1 to 6 of 6
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Footers via VBA (XL XP)

    I am trying to set up some code that changes footers on all the tabs. My code looks like this ...
    I've replaced the [ and ] with ( and ) so that it doesn't display the special woody items like <img src=/S/grin.gif border=0 alt=grin width=15 height=15> - how do I turn the tags off?

    <pre> With ActiveSheet.PageSetup
    .LeftFooter = "&(FILE)"
    .CenterFooter = "&(TIME)"
    .RightFooter = "&[DATE]"
    End With</pre>


    Then I run that on the current sheet and then go and look at the results using File, Setup and select the Header/Footer tab, it looks like this ...
    <IMG SRC=http://www.geocities.com/ruff_hi/woody/footer1.jpg>
    If I open the custom footer dialog, it looks like this ...
    <IMG SRC=http://www.geocities.com/ruff_hi/woody/footer2.jpg>
    When I close the custom footer dialog, it looks like this (file name removed) ...
    <IMG SRC=http://www.geocities.com/ruff_hi/woody/footer3.jpg>

    So, as far as I can see, the code I entered is correct but it is not 'taking' - any suggestions?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Footers via VBA (XL XP)

    You can use the special "tag" tags: <!t>[t]<!/t> will be turned into [ and <!t>[/t]<!/t> will be turned into ]. You can type them yourself, or use TAG from the 1-Click TagPanel.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Footers via VBA (XL XP)

    The codes you see in the page setup dialog are different from what VBA expects:
    <pre> With ActiveSheet.PageSetup
    .LeftFooter = "&F"
    .CenterFooter = "&T"
    .RightFooter = "&D"
    End With</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Footers via VBA (XL XP)

    <!t>[grin]<!/t> - thx Hans - works like a dream
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Footers via VBA (XL XP)

    Try

    .LeftFooter = "&F"
    .CenterFooter = "&T"
    .RightFooter = "&D"

  6. #6
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Footers via VBA (XL XP)

    unbelievable! I was planning to give the user control on the set up of these footers and then copy the footers that they have set up on one tab to all tabs. However, vba returns "&<!t>[DATE]<!/t>" for the date function within a footer but it needs "&D" to initialize the date function within the footer.

    I guess that I will have to scan the users selected footers and replace all "&<!t>[DATE]<!/t>" like items with "&D".

    Link to the MS KB on XL2000: Inserting and Formatting Text in Headers and Footers
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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