Give multiple charts on a worksheet/workbook same header or footer

[Guest]

Using Excel 2003, SP1

I'm creating many charts throughout a large workbook that has many
worksheets, and there are several charts on each worksheet. It is a pain to
set the header and footer individually for each chart.

Can I group these charts together (possibly only in the active worksheet at
any one time) to give them all the same header and footer, as I can group
worksheets together in a workbook, to give them all the same header and
footer?

TIA

posted first in Excel Charts

 

[Debra Dalgleish]

You could use programming to add a footer to all the charts and chart
sheets. For example:

'========================
Sub ChartFooters()

Dim ws As Worksheet
Dim chObj As ChartObject
Dim ch As Chart

For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
With chObj.Chart.PageSetup
.CenterFooter = "DRAFT COPY"
.RightFooter = "Page &P"
End With
Next chObj
Next ws

For Each ch In ActiveWorkbook.Charts
With ch.PageSetup
.CenterFooter = "DRAFT COPY"
.RightFooter = "Page &P"
End With
Next ch

End Sub
'============================

 

[Guest]

Thanks, Debra, with your advice I did the following, which changes the
header or footer on all charts - just what I wanted!

footer:
==========================================
Sub ChartFooters()

Dim ws As Worksheet
Dim chObj As ChartObject
Dim ch As Chart

For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
With chObj.Chart.PageSetup
.LeftFooter = "&8" & "Prepared by yourname yourdate"
.RightFooter = "&8" & "Printed &T on &D"
End With
Next chObj
Next ws

For Each ch In ActiveWorkbook.Charts
With ch.PageSetup
.LeftFooter = "&8" & "Prepared by yourname yourdate"
.RightFooter = "&8" & "Printed &T on &D"
End With
Next ch

End Sub
===================================================

and for the headers
===================================================



Sub ChartHeaders()

Dim ws As Worksheet
Dim chObj As ChartObject
Dim ch As Chart

For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
With chObj.Chart.PageSetup
.LeftHeader = "&8 " & Application.ActiveWorkbook.FullName
.RightHeader = "&A"
End With
Next chObj
Next ws

For Each ch In ActiveWorkbook.Charts
With ch.PageSetup
.LeftHeader = "&8 " & Application.ActiveWorkbook.FullName
.RightHeader = "&A"
End With
Next ch

End Sub

======================================================


Next question: how can I print date in long form (i.e. dddd, mmm dd, yyyy)
in place of &D which returns 04/07/2005?

 

[Debra Dalgleish]

You're welcome, and thanks for posting your solution. You could combine
the two macros into one, and it might be slightly faster to run. I've
changed the date in the following code, so it will print the long date
format that you want.

'===================
Sub ChartFootersHeaders()

Dim ws As Worksheet
Dim chObj As ChartObject
Dim ch As Chart

For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
With chObj.Chart.PageSetup
.LeftFooter = "&8" & "Prepared by yourname yourdate"
.RightFooter = "&8" & "Printed &T on " _
& Format(Date, "Long Date")
.LeftHeader = "&8 " & Application.ActiveWorkbook.FullName
.RightHeader = "&A"
End With
Next chObj
Next ws

For Each ch In ActiveWorkbook.Charts
With ch.PageSetup
.LeftFooter = "&8" & "Prepared by yourname yourdate"
.RightFooter = "&8" & "Printed &T on " _
& Format(Date, "Long Date")
.LeftHeader = "&8 " & Application.ActiveWorkbook.FullName
.RightHeader = "&A"
End With
Next ch

End Sub
'=====================

 

[Guest]

Perfect, Debra!

I don't want to be greedy, but can I add the day of the week to the date
string (as in Monday, July 4, 2005)?

Thanks so much for your help, I've learned a lot - I've always kept away
from the programming, and can see what I've been missing! I need to study up
on this, from the begining...

 

[Debra Dalgleish]

You're welcome! My Long Date format includes the day of the week, but
since yours doesn't, you can specify the exact format that you want, e.g.:

.RightFooter = "&8" & "Printed &T on " _
& Format(Date, "dddd, mmm dd, yyyy")

 

[Guest]

Thanks again Debra, I thought it might be that simple, but after quite a few
tries to get it, I'd given up and asked!

I haven't managed to set up my longdate format this way, (WinXP) although
I'd like to; how do you do this?

p.s. any suggestions for how to go about studying up on VB, from the
begining, will be much appreciated.

 

[Guest]

I found the WinXP location, in "Customise Regional Settings"

Thanks for all your help, Debra.
Ches, in London, ON.

 

[Debra Dalgleish]

Thanks for letting me know that you found the date setting.

For online information on VBA, see David McRitchie's list of tutorials

http://www.mvps.org/dmcritchie/excel/excel.htm#vbatutorials

For books, there's a list on my web site:

http://www.contextures.com/xlbooks.html

John Walkenbach's books are good, and so is Microsoft Excel 2002 Visual
Basic for Applications Step by Step by Reed Jacobson. If possible,
browse through a few at the bookstore, and see which style suits you.

And it's always nice to help a neighbour <g> -- I'm in Mississagua.