Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Variable Header For Printing

  1. #1
    Join Date
    6th January 2008
    Posts
    14

    Variable Header For Printing

    Good day,

    I have a multitude of reports to work on. Right now, the headers are made manually using the first few rows of each sheet, which is very inefficient, and difficult to get them to look the same. If the sheet is put on "Fit to X pages", the header is shrunk still.

    The reason this is done is because in the header / footer section of Excel 2002/2003, you can only use static text and predefined footer data (pg 1 of X, date, etc).

    Basically, I'd like to use a header that stays the same size, format, look between sheets/workbooks, but can accept variables in some way.

    Any ideas?

    Thanks in advance.

    -Matt

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Header Variables

    You would need to use VBA to retrieve the values of the variables and set the headers in the BeforePrint event. Would that be acceptable?
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  3. #3
    Join Date
    6th January 2008
    Posts
    14

    Re: Header Variables

    That sure will...

    I've used Excel/VBA for a few years now in various jobs, but there's still tons to learn..

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Header Variables

    Can you post an example of what you have now, and what you would prefer, including a manually-cobbled header that you want to automate?
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  6. #6
    Join Date
    6th January 2008
    Posts
    14

    Re: Variable Header For Printing

    Right now I'm using examples on this site to help me with the somewhat confusing/picky notation of the header/footer strings.

    Anyway, this is what I have so far:

    VB:
     
    With Worksheets("Test Header").PageSetup.CenterHeaderPicture 
        .FileName = "H:\Users\M_Lawson\Other Info\Logos\Logo.jpg" 
        .Height = 40 
        .Width = 70 
    End With 
     
     'set the left, right, and center text.
    Worksheets("Test Header").PageSetup.CenterHeader = "&G""&""Arial,bold""&12 Brake Dynamometer Testing" 
    Worksheets("Test Header").PageSetup.RightHeader = "&""Arial""&12 Cust. Ref: " & Sheets("Cover").Range("CustTestRef").value 
    Worksheets("Test Header").PageSetup.LeftHeader = "&""Arial""&12 Test Req. #: " & Sheets("Cover").Range("LinkTestReq").value 
     
     
    Worksheets("Test Header").PageSetup.RightFooter = "&R" & "&Arial" & "&10" & "Page &P of &N" 
    Worksheets("Test Header").PageSetup.LeftFooter = "&L" & "&Arial" & "&10" & "Date Printed: " & "&D" 
    
    
    What I need is a way to put "Test Req. #:" and the value on seperate lines of the left header, and similarly for the right header. You can do this on the header/footer in excel, so I imagine it's just a property I'm missing... I'd also like the picture and center header centered, with the text below the image.
    Auto Merged Post;

    Also, is there a way to add borders to the headers? (i.e. put the left header data in a box).
    Auto Merged Post;

    One more followup:

    Is there a way to specify that the header/footer NOT scale with pages that are "fit to print"?

    It seems by default, they are .

    Thanks again.

    -Matt
    Last edited by MedievalMan; January 8th, 2008 at 03:07. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Variable Header For Printing

    a way to put "Test Req. #:" and the value on separate lines of the left header
    VB:
    ActiveSheet.PageSetup.LeftHeader = _ 
    "&""fontName[,Regular|,Bold|,Italic|,Bold Italic]""[&fontSize]Line1 text" & Chr(10) _ 
    & "&""fontName[,Regular|,Bold|,Italic|,Bold Italic]""[&fontSize]Line2 text" 
    
    
    For example,
    VB:
    ActiveSheet.PageSetup.LeftHeader = _ 
    "&""Arial""&11This is 11 point Arial" & Chr(10) _ 
    & "&""Courier New,Bold Italic""No font size spec'd, so still 11 point" & Chr(10) _ 
    & "And a variable at the end: " & Range("A1").Value 
    
    
    I'd also like the picture and center header centered
    You should be able to record that and take it from there.
    Also, is there a way to add borders to the headers?
    Not that I know of.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  8. #8
    Join Date
    6th January 2008
    Posts
    14

    Re: Variable Header For Printing

    Thanks for your help SHG.

    Any ideas on how to keep the scale of the header the same? (irregardless if the page is doing a fit to X pages)?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 6
    Last Post: October 13th, 2006, 18:06
  2. Insert Picture Into Header for Printing
    By Howard Pohlman in forum EXCEL HELP
    Replies: 4
    Last Post: August 11th, 2006, 21:52
  3. Header row multiple cells of text when printing.
    By Carla in forum Excel and/or Word Help
    Replies: 2
    Last Post: October 5th, 2004, 00:06
  4. [Solved] Printing : printing header
    By Dowser in forum EXCEL HELP
    Replies: 2
    Last Post: January 22nd, 2004, 21:18
  5. Replies: 2
    Last Post: July 25th, 2003, 01:09

Bookmarks

Posting Permissions

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