Footers and Headers

[Avi]

Hi,

Can anyone tell me if there is a way to make the footers and headers dynamic; that is, can we make the text in the footer or header be dependent on the value of a given cell reference. For example, every time I change the value in cell A1 in some sheet, I would like the footer's text assume the new value in that cell.

Thanks in advance

Avi

[Paul B]

This will put what’s in cell A1 in the left hand header
Sub header()
With ActiveSheet.PageSetup
.LeftHeader = ActiveSheet.Range("A1")
End With
End Sub

[sann0638]

Is it possible to do this without macros?

[lenze]

You can also run Paul's code using the before print event

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = Range("A1")
End Sub

[Ivan F Moala]

On 2002-05-21 08:31, Avi wrote:
Hi,

Can anyone tell me if there is a way to make the footers and headers dynamic; that is, can we make the text in the footer or header be dependent on the value of a given cell reference. For example, every time I change the value in cell A1 in some sheet, I would like the footer's text assume the new value in that cell.

Thanks in advance

Avi

Only Via VBA.
Would suggest doing this via the workbooks sheet change event.
Place in the Thisworkbook object
The routine will setup (in thiscase)
the Footer to A1...change as rrquired.

eg

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Address = "$A$1" Then

        With ActiveSheet.PageSetup

            '## footers here ##

            '.LeftFooter = "&""Arial Black,Bold""&12" & [A1]

            .CenterFooter = "&""Arial Black,Bold""&8 " & [A1]

            '.RightFooter = "&""Times New Roman,Bold""&16" & [A1]

        End With

    End If

End Sub