Link page header with cell contents

[Matthew]

Is there a way to link the contents of a cell with the page header so that it
can change automatically when the cell changes. Also this would allow us to
change one cell and then link it to the header on several pages.

Thank you.

 

[Dave Peterson]

Instead of changing the header each time the cell changes, you could change the
header when you do a print (or printpreview).

Kind of like:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1")
.PageSetup.LeftFooter = .range("a1").value
End With
End Sub


This goes into the ThisWorkbook module.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Matthew]

Thank you Dave... that's a great idea. I feel stupid in that it seems so
simple and obvious, I should have realized it sooner. But sometimes writing
code all day, I lose track of the simplest answers expecting it to be
something much more complex than it really is.

Thanks again.

Instead of changing the header each time the cell changes, you could change the
header when you do a print (or printpreview).

Kind of like:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1")
.PageSetup.LeftFooter = .range("a1").value
End With
End Sub


This goes into the ThisWorkbook module.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Jim]

Is there a way to do this without using a macro?

 

[RCDWESTFIELD]

Dave,
can you also code font and font size into this program?
Thanks Bob

Instead of changing the header each time the cell changes, you could change the
header when you do a print (or printpreview).

Kind of like:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1")
.PageSetup.LeftFooter = .range("a1").value
End With
End Sub


This goes into the ThisWorkbook module.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

..PageSetup.LeftFooter = "&""Algerian,Regular""&16" & Range("A1").Value


Gord Dibben MS Excel MVP

Dave,
can you also code font and font size into this program?
Thanks Bob

 

[Bryan W.H.]

Expanding on this topic.

I'm using concantenate to set up the Header I want to use. Example would be
'April Year to Date" How can i turn this into a two line header?
Specifically..

April
Year to Date

The code is retrieving data from one cell. I thought about setting up the
data on two cells but i still can't figure out how to set up two lines in the
header.

Bryan

..PageSetup.LeftFooter = "&""Algerian,Regular""&16" & Range("A1").Value


Gord Dibben MS Excel MVP

Dave,
can you also code font and font size into this program?
Thanks Bob

 

[Dave Peterson]

"April Year" & vblf & "to Date"

Expanding on this topic.

I'm using concantenate to set up the Header I want to use. Example would be
'April Year to Date" How can i turn this into a two line header?
Specifically..

April
Year to Date

The code is retrieving data from one cell. I thought about setting up the
data on two cells but i still can't figure out how to set up two lines in the
header.

Bryan

..PageSetup.LeftFooter = "&""Algerian,Regular""&16" & Range("A1").Value


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Bryan

I would do the concatenation in the header.

With April in A1 and Year to Date in A2

Sub foo()
With ActiveSheet
..PageSetup.LeftHeader = Range("A1").Value & vbLf & Range("A2").Value
End With
End Sub


Gord

Expanding on this topic.

I'm using concantenate to set up the Header I want to use. Example would be
'April Year to Date" How can i turn this into a two line header?
Specifically..

April
Year to Date

The code is retrieving data from one cell. I thought about setting up the
data on two cells but i still can't figure out how to set up two lines in the
header.

Bryan

..PageSetup.LeftFooter = "&""Algerian,Regular""&16" & Range("A1").Value


Gord Dibben MS Excel MVP