Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ches
 
Posts: n/a
Default 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
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #3   Report Post  
Ches
 
Posts: n/a
Default


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?
  #4   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #5   Report Post  
Ches
 
Posts: n/a
Default

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


  #6   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #7   Report Post  
Ches
 
Posts: n/a
Default

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.

  #8   Report Post  
Ches
 
Posts: n/a
Default

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.

  #9   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Print all charts in a workbook (multiple worksheets) aewsaws Charts and Charting in Excel 4 May 12th 23 04:45 AM
Give multiple charts on a worksheet/workbook same header or footer Ches Charts and Charting in Excel 2 July 4th 05 05:58 PM
multiple pie of pie charts Nerissa Charts and Charting in Excel 1 June 11th 05 07:02 AM
Multiple charts in ChartSpace; problems with double Categories hstaaks Charts and Charting in Excel 0 May 4th 05 03:46 PM
Macro for multiple charts JS Excel Worksheet Functions 1 November 19th 04 04:44 AM


All times are GMT +1. The time now is 10:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"