Prev: Auto expanding X Axis for Chart
Next: Stacked and Single Column on the same axis
From: EAB1977 on 8 Mar 2010 10:48
I have Excel 2007 and trying to make this compatible with users that
have Excel 2002/2003.

I am having a hard time with the code below. When I change a specific
series from a stacked chart to a line and then try to change the
color, I am not successful. I am successful with changing the chart
type, however.

'Create the Average Line
.ActiveChart.PlotArea.Select
.ActiveChart.SeriesCollection.NewSeries
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).Values = "=Sheet2!R" & intRow
+ 1 & "C2:R" & intRow + 1 & "C" & rst.Fields.Count
.ActiveChart.SeriesCollection(y).Name = "=""Average"""
.ActiveChart.SeriesCollection(y).Select
.ActiveChart.SeriesCollection(y).AxisGroup = 1
.ActiveChart.SeriesCollection(y).ChartType = 4
.ActiveChart.SeriesCollection(y).Interior.Color = RGB(0, 0, 0)

I have tried everyhing and I can't figure it out. Any help you can
shed will be greatly apprecitated.
From: Jon Peltier on 9 Mar 2010 09:59
A line chart series has no Interior. Try MarkerBackgroundColor and
related properties.

Best approach when you're stuck on syntax is to open Excel 2003, turn on
the macro recorder, do what you want you program to do, and make use of
the recorded code.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/8/2010 10:48 AM, EAB1977 wrote:
> I have Excel 2007 and trying to make this compatible with users that
> have Excel 2002/2003.
>
> I am having a hard time with the code below. When I change a specific
> series from a stacked chart to a line and then try to change the
> color, I am not successful. I am successful with changing the chart
> type, however.
>
> 'Create the Average Line
> .ActiveChart.PlotArea.Select
> .ActiveChart.SeriesCollection.NewSeries
> y = .ActiveChart.SeriesCollection.Count
> .ActiveChart.SeriesCollection(y).Values = "=Sheet2!R"& intRow
> + 1& "C2:R"& intRow + 1& "C"& rst.Fields.Count
> .ActiveChart.SeriesCollection(y).Name = "=""Average"""
> .ActiveChart.SeriesCollection(y).Select
> .ActiveChart.SeriesCollection(y).AxisGroup = 1
> .ActiveChart.SeriesCollection(y).ChartType = 4
> .ActiveChart.SeriesCollection(y).Interior.Color = RGB(0, 0, 0)
>
> I have tried everyhing and I can't figure it out. Any help you can
> shed will be greatly apprecitated.
From: AdamV on 12 Mar 2010 09:09
Try something like this:
ActiveChart.SeriesCollection(y).Border.ColorIndex = 14


Yes, you need a good old colour index value to set this (if you need to
retain compatibility as you have said).

Jon P has some useful thoughts on palettes and a utility to change them
so you can get sensible colours too, from his website linked below.

Hope this helps

Adam

On 09/03/2010 14:59, Jon Peltier wrote:
> A line chart series has no Interior. Try MarkerBackgroundColor and
> related properties.
>
> Best approach when you're stuck on syntax is to open Excel 2003, turn on
> the macro recorder, do what you want you program to do, and make use of
> the recorded code.
>
> - Jon
> -------
> Jon Peltier
> Peltier Technical Services, Inc.
> http://peltiertech.com/
>
>
> On 3/8/2010 10:48 AM, EAB1977 wrote:
>> I have Excel 2007 and trying to make this compatible with users that
>> have Excel 2002/2003.
>>
>> I am having a hard time with the code below. When I change a specific
>> series from a stacked chart to a line and then try to change the
>> color, I am not successful. I am successful with changing the chart
>> type, however.
>>
>> 'Create the Average Line
>> .ActiveChart.PlotArea.Select
>> .ActiveChart.SeriesCollection.NewSeries
>> y = .ActiveChart.SeriesCollection.Count
>> .ActiveChart.SeriesCollection(y).Values = "=Sheet2!R"& intRow
>> + 1& "C2:R"& intRow + 1& "C"& rst.Fields.Count
>> .ActiveChart.SeriesCollection(y).Name = "=""Average"""
>> .ActiveChart.SeriesCollection(y).Select
>> .ActiveChart.SeriesCollection(y).AxisGroup = 1
>> .ActiveChart.SeriesCollection(y).ChartType = 4
>> .ActiveChart.SeriesCollection(y).Interior.Color = RGB(0, 0, 0)
>>
>> I have tried everyhing and I can't figure it out. Any help you can
>> shed will be greatly apprecitated.
 | 
Pages: 1
Prev: Auto expanding X Axis for Chart
Next: Stacked and Single Column on the same axis