I'm in the process of converting dynamically generated excel charts from
Excel 2003 to 2007. A couple problems I have resolved, but so far have not
been able to find a solution to a color repetition problem. The charts can
have anywhere from 2-3 legend entries to 15 -20. Problem is after the 1st
(lowest) three appear as individual colors, all the colors after that are the
same color. When this same chart is generated in 2003 no problems, 2007
repeating colors. So far I've tired to find some sort of legendkeys command
to set the colors to xlautomatic (which also works fine in 2003) but I can't
seem to find an example or the proper syntax to do this in 2007.

thanks,
jonathan

Hi,

I can not reproduce the problem with colours repeating.
But this code sets the colours to automatic.

Sub x()
Dim objSeries As Series

For Each objSeries In ActiveSheet.ChartObjects(1).Chart.SeriesCollection
objSeries.Interior.ColorIndex = xlAutomatic
Next
End Sub

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"jon18" wrote in message
...
I'm in the process of converting dynamically generated excel charts from
Excel 2003 to 2007. A couple problems I have resolved, but so far have
not
been able to find a solution to a color repetition problem. The charts can
have anywhere from 2-3 legend entries to 15 -20. Problem is after the 1st
(lowest) three appear as individual colors, all the colors after that are
the
same color. When this same chart is generated in 2003 no problems, 2007
repeating colors. So far I've tired to find some sort of legendkeys
command
to set the colors to xlautomatic (which also works fine in 2003) but I
can't
seem to find an example or the proper syntax to do this in 2007.

thanks,
jonathan

perfect! I had to put in some logic (application.version) so that it only
attempts this in 2007 as it generates errors in 2003, but it fixes it right
up.
the scale is set in code on auto open, so I wonder if that is why I'm seeing
the change.

With Worksheets(1).ChartObjects(2).Chart
.Axes(xlValue).MajorUnit = 20
.Axes(xlValue).MinimumScaleIsAuto = True
.Axes(xlValue).MaximumScaleIsAuto = True
.Axes(xlValue).MinimumScale = intMinScale
.Axes(xlValue).MaximumScale = intMaxScale
End With


Anyway, this definitely takes care of the problem as my users convert to
2007.

thanks again!!
jonathan


"Andy Pope" wrote:

Hi,

I can not reproduce the problem with colours repeating.
But this code sets the colours to automatic.

Sub x()
Dim objSeries As Series

For Each objSeries In ActiveSheet.ChartObjects(1).Chart.SeriesCollection
objSeries.Interior.ColorIndex = xlAutomatic
Next
End Sub

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"jon18" wrote in message
...
I'm in the process of converting dynamically generated excel charts from
Excel 2003 to 2007. A couple problems I have resolved, but so far have
not
been able to find a solution to a color repetition problem. The charts can
have anywhere from 2-3 legend entries to 15 -20. Problem is after the 1st
(lowest) three appear as individual colors, all the colors after that are
the
same color. When this same chart is generated in 2003 no problems, 2007
repeating colors. So far I've tired to find some sort of legendkeys
command
to set the colors to xlautomatic (which also works fine in 2003) but I
can't
seem to find an example or the proper syntax to do this in 2007.

thanks,
jonathan

Hi,

Glad it worked although I'm confused about why you got errors in xl2003 as I
used that version to record the base code.

Changing the axis properties would not effect a Area chart but it would a
Surface chart as the scale bands determine the colours.

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"jon18" wrote in message
...
perfect! I had to put in some logic (application.version) so that it
only
attempts this in 2007 as it generates errors in 2003, but it fixes it
right
up.
the scale is set in code on auto open, so I wonder if that is why I'm
seeing
the change.

With Worksheets(1).ChartObjects(2).Chart
.Axes(xlValue).MajorUnit = 20
.Axes(xlValue).MinimumScaleIsAuto = True
.Axes(xlValue).MaximumScaleIsAuto = True
.Axes(xlValue).MinimumScale = intMinScale
.Axes(xlValue).MaximumScale = intMaxScale
End With


Anyway, this definitely takes care of the problem as my users convert to
2007.

thanks again!!
jonathan


"Andy Pope" wrote:

Hi,

I can not reproduce the problem with colours repeating.
But this code sets the colours to automatic.

Sub x()
Dim objSeries As Series

For Each objSeries In
ActiveSheet.ChartObjects(1).Chart.SeriesCollection
objSeries.Interior.ColorIndex = xlAutomatic
Next
End Sub

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"jon18" wrote in message
...
I'm in the process of converting dynamically generated excel charts
from
Excel 2003 to 2007. A couple problems I have resolved, but so far have
not
been able to find a solution to a color repetition problem. The charts
can
have anywhere from 2-3 legend entries to 15 -20. Problem is after the
1st
(lowest) three appear as individual colors, all the colors after that
are
the
same color. When this same chart is generated in 2003 no problems,
2007
repeating colors. So far I've tired to find some sort of legendkeys
command
to set the colors to xlautomatic (which also works fine in 2003) but I
can't
seem to find an example or the proper syntax to do this in 2007.

thanks,
jonathan