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

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

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
'============================

Ches wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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?

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
'=====================

Ches wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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...

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")


Ches wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

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

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


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

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...m#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.


Ches wrote:
> I found the WinXP location, in "Customise Regional Settings"
>
> Thanks for all your help, Debra.
> Ches, in London, ON.
>
>
>
>>p.s. any suggestions for how to go about studying up on VB, from the
>>begining, will be much appreciated.
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html