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.

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

"Dean Hinson" wrote in message
...
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.

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" wrote:

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

"Dean Hinson" wrote in message
...
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.

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

"Dean Hinson" wrote in message
...
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" wrote:

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

"Dean Hinson" wrote in message
...
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 -

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/
_______

Peter T wrote:

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

"Dean Hinson" wrote in message
...

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" wrote:


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

"Dean Hinson" wrote in message
...

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.