Announcement

Collapse
No announcement yet.

Variable Header For Printing

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      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..

      Comment


      • #4
        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.

        Comment


        • #5
          Re: Header Variables

          Could you use Custom Views with http://www.ozgrid.com/forum/showthread.php?t=13668

          Comment


          • #6
            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:

            Code:
               
              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, 04:07. Reason: Auto Merged Doublepost

            Comment


            • #7
              Re: Variable Header For Printing

              a way to put "Test Req. #:" and the value on separate lines of the left header
              Code:
                      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,
              Code:
                  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.

              Comment


              • #8
                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)?

                Comment

                Working...
                X