Reference Cell in Header or Footer?

[Falcon99]

Is it possible to reference a cell value in the header or footer in a spreadsheet? If so, what is the syntax?

TIA!


Falcon

 

[DaleWatson123321]

Falcon,

Here's an example I put together awhile back. It was where the user wanted to tack on a number to "Company Name_", where the number came from a cell in the worksheet.

Note: This routine refers to a cell named "uname".

Sub Set_Footer()
unitname = Range("uname&quot.Value
ftr = "CompanyName_" & Str(unitname)
With ActiveSheet.PageSetup
.LeftFooter = "&""Arial,Regular," & ftr
End With
End Sub

I hope this helps. Please advise as to how it fits. If you require a more specific header/footer, please specify.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[carrr]

From

http://www.payneconsulting.com/payneconsultingold/office/Excel/exceltips2.htm

The header and footer cannot contain a link to a cell. You can create and run a macro that will put the value of a cell into a footer or header but it will not change dynamically. You'll need to run this macro each time the contents of the specified cell changes.

Sub AssignCenterHeaderToValueInA1OnActiveSheet()
ActiveSheet.PageSetup.CenterHeader = Range("A1&quot.Value
End Sub

 

[DaleWatson123321]

Falcon,

Here's another example, where the VBA routine places the contents of three cells into the LEFT, CENTER, and RIGHT Header positions.

Note that the date formula used here is "special" in that it provides "more flexibility" over the normal Excel data/time options.

For the following example, use these steps

1) Assign the following range names to three cells.

hdr_l, hdr_c, and hdr_r

2) In the cell named hdr_1, enter the text: "Left Header"

3) In the cell named hdr_c, enter the text: "Center Header"

4) In the cell named hdr_r, enter the formula:
=&quot;Printed: &quot;&RIGHT(&quot; &quot;&FIXED(IF(HOUR(NOW())=0,12,IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW()))),0,TRUE),2)&&quot;:&quot;&RIGHT(&quot;00&quot;&FIXED(MINUTE(NOW()),0,TRUE),2)&IF(HOUR(NOW())<12,&quot; a.m.&quot;,&quot; p.m.&quot&&quot; &quot;&CHOOSE(MONTH(NOW()),&quot;January&quot;,&quot;February&quot;,&quot;March&quot;,&quot;April&quot;,&quot;May&quot;,&quot;June&quot;,&quot;July&quot;,&quot;August&quot;,&quot;September&quot;,&quot;October&quot;,&quot;November&quot;,&quot;December&quot&&quot; &quot;&RIGHT(&quot;00&quot;&FIXED(DAY(NOW()),0,TRUE),2)&&quot;, &quot;&FIXED(1900+(YEAR(NOW())-1900),0,TRUE)

You'll obviously want to copy the formula from above, and paste it into your worksheet.

5) Also copy the following VBA routine

Sub SetPrintHeadings()

h_l = Range(&quot;hdr_l&quot.Value
h_c = Range(&quot;hdr_c&quot.Value
h_r = Range(&quot;hdr_r&quot.Value

With ActiveSheet.PageSetup
.LeftHeader = h_l
.CenterHeader = h_c
.RightHeader = h_r
End With

End Sub


Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Falcon99]

Dale Watson and Carrr,

Thanks a heap!
Just to fill you in I'm pushing data to an Excel template from Access and I wanted to update the title in the header.

I can see .CenterHeader is the way to go.


One final question. Using this method;

.LeftFooter = &quot;&&quot;&quot;Arial,Regular,&quot; & ftr

Is it possible to set the font size?

Thanks again,

Falcon

 

[DaleWatson123321]

Falcon,

To set the font size, use the following. I've included the full routine as before, but added a font size of &quot;20&quot;.

Sub Set_Footer()
unitname = Range(&quot;uname&quot.Value
ftr = &quot;CompanyName_&quot; & Str(unitname)
With ActiveSheet.PageSetup
.LeftFooter = &quot;&20&quot;&quot;Arial,Regular,&quot; & ftr
End With
End Sub

Hope this is what you wanted. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Falcon99]

Excellent! Thank you very much.


Falcon

 

[DaleWatson123321]

Falcon, and to anyone else who might be interested...

I misplaced the placing of the font size (I was in a rush to leave work for an appointment)

I rechecked the routine, and the following WORKS...

This example picks up whatever number is located in the cell named &quot;uname&quot;, and tacks it on to &quot;CompanyName_&quot;

And you can easily adjust the font size by replacing the &quot;20&quot; with whatever font size you prefer.

Sub Set_Footer()
unitname = Range(&quot;uname&quot.Value
ftr = &quot;CompanyName_&quot; & Str(unitname)
With ActiveSheet.PageSetup
.LeftFooter = &quot;&20&&quot;&quot;Arial,Regular,&quot; & ftr
End With
End Sub

You can also change the Font,
and Style - from Regular to Italics, Bold, or Bold Italic
e.g.
.LeftFooter = &quot;&20&&quot;&quot;Times New Roman,Bold Italic,&quot; & ftr

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Falcon99]

Cool, thanks.

Yeah, I got the size to change but I realized the font syntax was not correct. Thanks for posting it.


Falcon