Modifying Chart Series Attributes Insights (Color, Marker Type, Et

[Guest]

Using the following code in VBA from this forum, I was able to revise the
line chart series to adhere to presentation guidelines...

With Chart.SeriesCollection(i)
.Border.ColorIndex = Index
.Border.Weight = xlMedium
.MarkerStyle = xlNone
End With

However, I started getting a VBA error stating that the VBA code could no
longer revise the chart. For a while I could not figure why the code decided
not to work. After some research, I realized that the user has selected
December and that no values for December have been collected for the chart.
You cannot revise a series that has no values. But I still wanted the legend
to reflect the formatting requirements. Ah... the legend. After some
additional research, I changed the VBA code to .....

With Chart.Legend.LegendEntries(i).LegendKey
.Border.ColorIndex = Index
.Border.Weight = xlMedium
.MarkerStyle = xlNone
End With

This will work whether or not the series has values. And when you change the
legend key, it changes the seriescollection formats at the same.

So if you have a possibility that your chart series might not have any data
to chart but you want the series/legend to be formatted a certain way, I
think will do the trick.

If anyone has any comments, or if there is any other words of wisdom on this
subject, please post ot this thread.

Thank you in advance for taking the time to view this rambling.

 

[Peter T]

Hi Dean,

Haven't tried your code and not sure what i & index are (though I can take a
guess). But a quick comment -

You need to be a bit careful to assume the Legendkey index relates to the
same SeriesCollection index, if that's what you're assuming. Play with
following:

legend key is deleted but not series,
series deleted & new inserted,
series with multiple chart types, (bar, line etc)
series on different axis
trendlines

With a considerable amount of effort it's "almost" possible to be sure to
relate correct key to series. When you think you've catered for every
conceivable situation you'll find some other problem!

Regards,
Peter

 

[Guest]

Hi Peter,

Thanks for your input. The line charts that I was doing does handle the
situation of variable number of series, thus the (i). The index is the color
index so that always series 1 in red, series 2 is blue, etc.

Anyway, these charts do not get changed by the end user. The line charts
are part of a dashboard that we use for consistent reporting of metrics. So
I was not concerned too much about what you had listed regarding deletion of
the legend, et al. I was only trying to enlighted anyone who may follow down
the sme path I did and what I found out to make a better, more reliable (IMO)
solution.

However, as I stated in the original post comments and other viewpoints are
most welcomed. The more we share, the more we learn.

Thanks again!

 

[Peter T]

Hi Dean,

Yes, for a typical chart there's unlikely to be a problem, particularly one
you're in total control of.

The line charts that I was doing does handle the
situation of variable number of series, thus the (i). The index is the color
index so that always series 1 in red, series 2 is blue, etc.

I don't quite follow, colorindex 1 is black, 2 white, 3 red (default
palette).

FWIW the "xlAutomatic" colours for line series are applied to a new chart
like this:

colorX = (i + 24) Mod 56
where i is the series index.

If a series is deleted, the next colour of a new series will adopt the first
unused index starting from 25, which may not relate in series order. I
appreciate this will not occur for you but something to bear in mind.

Regards,
Peter

 

[Jon Peltier]

Peter -

Either he's changed the default palette, or his variable index is changed by code he
didn't post.

Dean -

Peter's point about relating legend keys to series is an important one, but if the
charts are created in code, and the formatting is done before the user has a chance
to mess anything up, you should be okay.

If registration of legend entry to series becomes a problem, you could always
temporarily assign dummy values to the series, reformat the series, then reassign
the original data.

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