Global settings for all charts in a workbook

  • Thread starter Thread starter LeAnne
  • Start date Start date
L

LeAnne

Hi all, LTNS (Yep, I'm back) -

I have a workbook (XL97) containing 44 charts, each in its own
worksheet. I would like to find a way to standardize the settings for
all charts in the workbook. That is, I want to apply uniform formatting
properties to all of the charts in the file without having to go to each
and every worksheet and do it manually. For example, to format the
horizontal & vertical axes to my preferences, I've been doing the very
tedious Right-click -> Format-Axis and setting the tick mark patterns,
font size & color, background color, etc. etc. manually for every!
single! chart!. I'm pretty much a code virgin (my limited experience has
only been with Access VBA anyway) & would rather not delve into the
mysteries of behind-the-GUI Excel VB if it can be avoided. Can anyone
offer any suggestions?

tia,

LeAnne
 
If I were in your shoes, I would do one of two things.

First, if all that is different about the charts is the data set shown,
consider a single chart with the data selected by some kind of user
choice -- basically, a variant of the Dynamic Charts page
(http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html)

Second, select a chart, turn on the macro recorder, do whatever changes
you want to make, and turn off the macro recorder. Now, select each
subsequent chart and run the macro. Or, share the code here and someone
can show you how to change every chart on every worksheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004
 
LeAnne -

If everything is the same for all charts, including axis titles and
chart titles, you can make one chart pretty, then select it and copy it,
select the next chart, choose Paste Special from the Edit menu, Formats
only. Repeat as needed.

I never have charts that all have the same titles, though, so I whipped
up this little macro, which stores the titles of each chart, then copies
the first chart, pastes the formats onto the next chart, and puts the
original titles back.

To run it, start in Excel, press Alt+F11 to open the VB Editor. Choose
Module from the Insert menu, and paste this code into the code window
that opens. Go back to Excel, press Alt+F8, select the AllChartsTheSame
macro in the listing (it's probably the only one listed if you don't
write macros), and press Run.

Sub AllChartsTheSame()
Dim sXAxis As String
Dim sYAxis As String
Dim sTitle As String
Dim cht As Chart
For Each cht In ThisWorkbook.Charts
If cht.Name <> ThisWorkbook.Charts(1).Name Then
sXAxis = ""
sYAxis = ""
sTitle = ""
If cht.HasTitle Then sTitle = cht.ChartTitle.Text
If cht.Axes(xlCategory).HasTitle Then _
sXAxis = cht.Axes(xlCategory).AxisTitle.Text
If cht.Axes(xlValue).HasTitle Then _
sYAxis = cht.Axes(xlValue).AxisTitle.Text
ThisWorkbook.Charts(1).ChartArea.Copy
cht.Paste Type:=xlFormats
If Len(sTitle) > 0 Then _
cht.ChartTitle.Text = sTitle
If Len(sXAxis) > 0 Then _
cht.Axes(xlCategory).AxisTitle.Text = sXAxis
If Len(sYAxis) > 0 Then _
cht.Axes(xlValue).AxisTitle.Text = sYAxis
End If
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi Jon & Tushar, thanks for replying.

Jon Peltier said:
LeAnne -

If everything is the same for all charts, including axis titles and
chart titles, you can make one chart pretty, then select it and copy it,
select the next chart, choose Paste Special from the Edit menu, Formats
only. Repeat as needed.

I never have charts that all have the same titles, though, so I whipped
up this little macro, which stores the titles of each chart, then copies
the first chart, pastes the formats onto the next chart, and puts the
original titles back.
Click to expand...

Wow, you threw that code together just like that? I'm impressed (maybe
even a tad jealous!). It works like a charm, but I'm afraid it doesn't
achieve the results I had in mind. You're correct that each chart has a
different title. But some also have different scales on the y-axis, as
well. To be more specific: the charts are box-and-whisker plots (created
using your technique, Jon!) in groups of 4; 1 set of 4 box plots for a
biological metric (say, EPT), and each of the 4 in the set showing
metric results for a different ecoregion (EPT - Ecoregion 64, EPT -
Ecoregion 66, &c). So each of the 4 ecoregion plots for a particular
metric have their y-axes set to the same scale, but scales differ from
metric set to metric set (e.g. the EPT charts are all set from 0-15 in
increments of 5, but %EPT is set from 0.0 to 1.0 in increments of 0.10.
This also means that the 4 plots in a metric set are labelled
identically on the y-axis (e.g. "EPT Taxa", "PercentEPT"). When I run
the module, it sets ALL of the y-axes for all charts in the workbook to
the scale of the first chart (which happens to be EPT). And
unfortunately, it also makes the boxes and whiskers go kablooey. Ideas?

Thanks for your help,

LeAnne
 
Leanne -

I just used a few lines of code to allow the quick and dirty copy/paste
formats approach not to mess up the chart and axis titles. Works okay
for what I intended it for.

You would have to either enhance this macro to save the axis scale
settings, and any other item that isn't the same from chart to chart, or
write another macro that applies the same settings to each chart without
touching the things that pasted formats destroy. To do this, you'd turn
on the macro recorder while applying the settings you cared about, after
everything else was ready. Then you would go through this code and
remove references to things you didn't want included. Then adjust it to
work on all charts, not just a selected chart.

It goes a bit beyond a casual newsgroup post, but give it a go. When you
hit a rough stretch, come back with questions.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon said:
Leanne -

I just used a few lines of code to allow the quick and dirty copy/paste
formats approach not to mess up the chart and axis titles. Works okay
for what I intended it for.

You would have to either enhance this macro to save the axis scale
settings, and any other item that isn't the same from chart to chart, or
write another macro that applies the same settings to each chart without
touching the things that pasted formats destroy. To do this, you'd turn
on the macro recorder while applying the settings you cared about, after
everything else was ready. Then you would go through this code and
remove references to things you didn't want included. Then adjust it to
work on all charts, not just a selected chart.

It goes a bit beyond a casual newsgroup post, but give it a go. When you
hit a rough stretch, come back with questions.

- Jon
Click to expand...

Er...sorry about that. I was kinda hoping a fix would be relatively
simple. But I can see this would be a fershlugginer piece of code, even
for an MVP. And as I said, I'm a VB ignoramus...I don't really have the
knowledge/skill to create a class module for all objects in the
workbook's "charts" collection to set the named attributes of each
object to my preferences (geez, it's painful just writing about it!).
I'll just format the chart properties manually; it's only a one-time do,
& it really won't take all _that_ long.

Thanks again for trying to help,

LeAnne
 
LeAnne -

It's not that painful. Record a macro, make it general, so it doesn't
refer to a particular chart. then at the top of the macro, insert a few
lines:

Dim Cht as Chart
For Each Cht in ActiveWorkbook.Charts

then at the end, insert this line:

Next

(goes with For Each... above)

Then make all the macro's references to ActiveChart refer to Cht instead.

No ugly class modules needed, just the simple chart variable, Cht.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top