Announcement

Collapse
No announcement yet.

Formulas: In a header or footer

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

  • Formulas: In a header or footer

    Is it possible to index a header to a cell in a spreadsheet.

    I would like to create a template whereby every tab has the same header indexed to a cell on the tab that contrains the company information.

  • #2
    Have a look at my example here

    http://ozgrid.com/forum/viewthread.php?tid=2952
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Great tutorial on Header/Footer setup, Roy.

      Dennis - if the cell info you want to put in your Header/Footer may be changing, you might consider putting the code in the WorkbookBeforePrint event so its updated before you print.

      ....Ralph

      Comment


      • #4
        Another solution to look at for Another Dennis!

        http://ozgrid.com/forum/viewthread.php?tid=4391

        HTH
        Brandtrock

        Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

        Comment


        • #5
          Re: Formulas: In a header or footer

          Dear all,

          I have a excel sheet2, which has header.

          I want to copy cell B5 of sheet1 in sheet2 header.

          What I have done so far is written a code in VB to put cell B5 value in the header.

          The code goes as follows in sheet2:

          Code:
           Private Sub Worksheet_Activate()ActiveSheet.PageSetup.LeftHeader = Format(Worksheets("sheet1").Range("B5").Value)End Sub

          Now this works fine in showing value of B5, but it does not take the format.

          ie:

          sheet1 , B5 has date with format: Friday, 12, May, 2006

          whereas when i put the above code to show the same value in header of sheet2 it shows me:

          12/05/2006

          Anybody, any idea how can i keep the same format as cell B5 of sheet1??

          Any help will be appreciated.

          Thank you

          Comment


          • #6
            Re: Formulas: In a header or footer

            Dear all,

            I have a excel sheet2, which has header.

            I want to copy cell B5 of sheet1 in sheet2 header.

            What I have done so far is written a code in VB to put cell B5 value in the header.

            The code goes as follows in sheet2:

            Code:
            Private Sub Worksheet_Activate()
            
            ActiveSheet.PageSetup.LeftHeader = Format(Worksheets("sheet1").Range("B5").Value)
            
            End Sub

            Now this works fine in showing value of B5, but it does not take the format.

            ie:

            sheet1 , B5 has date with format: Friday, 12, May, 2006

            whereas when i put the above code to show the same value in header of sheet2 it shows me:

            12/05/2006

            Anybody, any idea how can i keep the same format as cell B5 of sheet1??

            Any help will be appreciated.

            Thank you

            Comment


            • #7
              Re: Formulas: In a header or footer

              Hi,

              Try,
                  ActiveSheet.PageSetup.LeftHeader = Worksheets("sheet1").Range("B5").Text 

              Cheers
              Andy

              Comment


              • #8
                Re: Formulas: In a header or footer

                Fab,thanks andy it does work.

                Although it looses the format everytime date is changed in sheet1 B5

                I tried putting some code in to get bold and font size, unfortunately it gives me error message. I guess I am not putting the correct vb code. I want to have font size: 20, Font: Arial, and Bold.

                Code:
                Private Sub Worksheet_Activate()
                
                ActiveSheet.PageSetup.LeftHeader = Worksheets("Start Page&B").Range("C3").Text
                
                End Sub
                Does anyone know how can i get formats right?

                Thank you in advance

                Comment


                • #9
                  Re: Formulas: In a header or footer

                  This code seems to be not working either. I thought following code will give me Font: arial, Size: 18 and In bold. But it is not working!!

                  Code:
                  ActiveSheet.PageSetup.LeftHeader = Worksheets("sheet1&B&""Arial""&18").Range("B5").Text
                  It gives me error. any suggestions?

                  Comment


                  • #10
                    Re: Formulas: In a header or footer

                     Private Sub Worksheet_Activate()

                    Dim strHeader As String

                    With Worksheets("Start Page&B").Range("C3") ' change to whatever the correct sheetname should be
                    strHeader = "&""" & .Font.Name & """" ' fontname
                    strHeader = strHeader & " &" & Format$(.Font.Size, "00") ' fontsize
                    If .Font.Bold Then strHeader = strHeader & " &B" ' fontbold
                    strHeader = strHeader & " " & .Text ' text from C3
                    End With

                    ActiveSheet.PageSetup.LeftHeader = strHeader

                    End Sub
                    Last edited by Andy Pope; May 12th, 2006, 23:44.

                    Cheers
                    Andy

                    Comment


                    • #11
                      Re: Formulas: In a header or footer

                      On the other hand, if you're trying to pull just the value and number format, and keep it at Arial 20 Bold, this should work (if the name of the sheet for the cell is "Start Page"):
                      Code:
                      ActiveSheet.PageSetup.LeftHeader = "&""Arial,Bold""&20 " & Format(Sheets("Start Page").Range("C3").Value, Sheets("Start Page").Range("C3").NumberFormat)
                      Brian
                      >DISABLE DALEK WITH SONIC SCREWDRIVER
                      The batteries have gone flat. I told you that you should have changed them after you spent ten hours debugging it in the TARDIS. But did you listen? Noooooooo...

                      The Dalek notices your presence for the first time, and reacts normally.
                      "EXTERMINATE! EXTERMINATE!"

                      *** You have been exterminated ***

                      Comment


                      • #12
                        Re: Formulas: In a header or footer

                        Thank you Andy,

                        That works. Didn't realise it will be this complicated code for such simple formatting!!

                        Regards
                        B Kamle

                        Comment


                        • #13
                          Re: Formulas: In a header or footer

                          Thanks Brian,

                          Your code works brill too, it is much easier to understand and comfortable to manage/make changes.

                          Regards,
                          B Kamle

                          Comment


                          • #14
                            Re: Formulas: In a header or footer

                            The complexity in my code example is that it is dynamic with reference to the font characteristics of the cell C3.

                            If you are happy with fixed font attributes then yes the explicit code sample is easier.

                            Cheers
                            Andy

                            Comment


                            • #15
                              Re: Formulas: In a header or footer

                              Yea, I noticed that. It is very useful for dynamic changes. Just didnt realise it will be this complex but i do understand the code. The code was really helpful.
                              Thanks

                              Comment

                              Working...
                              X