Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Headers & Footers (2000)

    My header and footer seem not to be connected to the Page Setup margins !
    How can I set the margins ie. the width of the header and footer ?

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Headers & Footers (2000)

    What is the problem? The header and footer are too wide for the margins? Then put paragraph returns in the text.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Headers & Footers (2000)

    There aren't separate margins for the header and footer. Can you explain what your are trying to do?
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Headers & Footers (2000)

    I have an Excel template Book.xlt from which I insert Standard Company Headers and Footers containing Path & File and Page X of Y in the footer and Company Name, Sheet Name and Revision Information (dependant on Location) in the Header.
    When the macros are inserted in a previously created workbook then the file re-opened to create the header and footer.
    the header and footer widths do not match the Page width of the previously created Workbook.

    Code for Book.xlt is attached put in thisworkbook
    Attached Files Attached Files

  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: Excel Headers & Footers (2000)

    I have been looking for something better, but have so far come up only with this. The str8HardSpaces are entered by using Alt-0160 assuming Arial Font.

    Const str8HardSpaces As String = "
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Headers & Footers (2000)

    I have just discovered that there are in fact separate margins for header and footer. You can set them like this if it will help:

    [pre]
    Worksheets("Sheet1").PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
    Worksheets("Sheet1").PageSetup.FooterMargin = Application.InchesToPoints(0.5)
    [/pre

    Sorry for my previous incorrect statement.
    Legare Coleman

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

    Re: Excel Headers & Footers (2000)

    Edited some typos

    According to the online help (for Excel 97, admittedly), HeaderMargin is the distance between the top of the page and the header; analogously for the FooterMargin.

    Ralph wants to modify the width of the header and footer. Header and footer don't use the left and right margin you set for the worksheet, and there is way to set the left and right margins for the header and footer. I think that is why JohnBF suggested adding leading and trailing spaces.

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

    Re: Excel Headers & Footers (2000)

    Legare, that is good to know, but if I understand correctly, those margins set the distance of the header and footer from the top and bottom of the page edge, whereas if I understood the original poster, his objective is to get the margin for the left and right edges of the header and footer aligned with the left and right margins for the text body.

    (Or am I off track again? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>)
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Headers & Footers (2000)

    Sorry, I misunderstood what he was asking. I think that your suggestion is the best available.
    Legare Coleman

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

    Re: Excel Headers & Footers (2000)

    John,

    Leading spaces are fine if you need to indent the margin, but I do not think you can align the footer margins with the page margins if you elect to have very narrow margins set for th epage. See the attached. Or is there a way ?

    Andrew
    Attached Images Attached Images

  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: Excel Headers & Footers (2000)

    No way to "outdent" them that I can think of. When I was looking at this question, I was thinking of the tricks you could do with ANSI customizing the DOS Prompt, etc., using the backspace and tab lower ASCII characters, but they do not work in this context. Once in a while I miss DOS.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Headers & Footers (2000)

    I think that the only way to do an outdent would be to make the page left and right margins as wide as needed for the header and footer and then either put empty columns on the left and right. Using PageSetup to set "Center on page horizontally" might also work.
    Legare Coleman

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

    Re: Excel Headers & Footers (2000)

    Legare,

    I think the problem is the Left & Right Header / Footer margins are fixed, so if you set the left and right page margins to say .5, you are stuck with footer margins of 1.9 (in my case anyway). Are there some settings somewhere that can be tweaked ?

    Andrew

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Headers & Footers (2000)

    Oh, I thought that the header and footer margins moved with the page margins. If that is the case, I don't know of any way to change it.
    Legare Coleman

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

    Re: Excel Headers & Footers (2000)

    Another weakness in Microsoft Excel's Printing capabilities.

    Another one that REALLY bugs me is that the Page Setup Dialog with multiple sheets does not warn the user or acknowledge (by greyout or blank) that different H's & F's exist on certain sheets. So in complex situations if you want to change just one H & F element in all sheets, but not all elements are the same, you have to go sheet by sheet; if you try a "broadcast" (multi-sheet) edit, all sheets will be reset to the active sheet you edit. I have burned a bunch of time and learned a hard lession with that issue when I had some major projects I had to publish for inclusion in legal contracts.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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