Formulas: In a header or footer

[Another Dennis]

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.

[royUK]

Have a look at my example here

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

[rem1224]

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

[Brandtrock]

Another solution to look at for Another Dennis!

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

HTH

[kamle_b]

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:

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

[kamle_b]

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:

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

[Andy Pope]

Hi,

Try,

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

[kamle_b]

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.

 
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

[kamle_b]

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!!

 
ActiveSheet.PageSetup.LeftHeader = Worksheets("sheet1&B&""Arial""&18").Range("B5").Text 

It gives me error. any suggestions?

[Andy Pope]

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