VBA - Adding Footers and Headers

Anonymous · Feb 7th 2003

Using VBA, is a way to add/update headers and footers based on the value in a particular worksheet cell? Thankyou.

smozgur · Feb 7th 2003

Hi,

This code will update Sheet1's center header by changing A1 cell.

'Code goes to Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Sheet1.PageSetup.CenterHeader = Sheet1.Range("A1").Value
End If
End Sub

Code line also can be changed like below to format font.

Sheet1.PageSetup.CenterHeader = "&""Arial,Bold""&9" & Sheet1.Range("A1").Value

You can also modify this code to set same font format with the source cell by using parameters for fontname, bold and size values.

I hope this helps.

Anonymous · Feb 7th 2003

Thanks for the great help smozgur. That resolves my question perfectly.

smozgur · Feb 8th 2003

You're welcome, Sunrise.

rachel2000 · Feb 20th 2003

Hi
I did not understand how to enter the header with vba
do i need to privat sub or can i put it
in a regular sub, and if so how
thanks

VBA - Adding Footers and Headers

Participate now!

Share