Changing line color of a graph with VBA

[craigwg]

I am making a Scrabble score tracker and I have some charts I need to create dynamically. I also need to adjust the colors but after a few hours of research I still can't successfully do it. Here is the code I have:



Sub createScoreChart()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.PlotVisibleOnly = False
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Scrabble'!$H$7"
ActiveChart.SeriesCollection(1).Values = "='Scrabble'!$B$10:$S$10"
ActiveChart.SeriesCollection(1).Line.Color = "red"

ActiveChart.SeriesCollection(1).Interior.ColorIndex = 5 ''This is the line in question

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Scrabble'!$H$11"
ActiveChart.SeriesCollection(2).Values = "='Scrabble'!$B$14:$S$14"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "='Scrabble'!$H$15"
ActiveChart.SeriesCollection(3).Values = "='Scrabble'!$B$18:$S$18"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "='Scrabble'!$H$19"
ActiveChart.SeriesCollection(4).Values = "='Scrabble'!$B$22:$S$22"
End Sub



Any help would be great on this. I realize I need to make my code more efficient and use with statements. I'll put those in later. Gotta get it working first, right?

[Artik]

In a line chart unless you can not set the fill color line. At least I do not see such a possibility.

Artik

[Bob Phillips]

Sub createScoreChart()
ActiveSheet.Shapes.AddChart.Select
With ActiveChart

.ChartType = xlLine
.PlotVisibleOnly = False

.SeriesCollection.NewSeries
With .SeriesCollection(1)

.Name = "='Scrabble'!$H$7"
.Values = "='Scrabble'!$B$10:$S$10"
.Border.ColorIndex = 5
End With

.SeriesCollection.NewSeries
With .SeriesCollection(2)

.Name = "='Scrabble'!$H$11"
.Values = "='Scrabble'!$B$14:$S$14"
End With

.SeriesCollection.NewSeries
With .SeriesCollection(3)

.Name = "='Scrabble'!$H$15"
.Values = "='Scrabble'!$B$18:$S$18"
End With

.SeriesCollection.NewSeries
With .SeriesCollection(4)

.Name = "='Scrabble'!$H$19"
.Values = "='Scrabble'!$B$22:$S$22"
End With
End With
End Sub

[Bob Phillips]

And a bit tidiere



Sub createScoreChart()
ActiveSheet.Shapes.AddChart.Select
With ActiveChart

.ChartType = xlLine
.PlotVisibleOnly = False

With .SeriesCollection.NewSeries

.Name = "='Scrabble'!$H$7"
.Values = "='Scrabble'!$B$10:$S$10"
.Border.ColorIndex = 5
End With

With .SeriesCollection.NewSeries

.Name = "='Scrabble'!$H$11"
.Values = "='Scrabble'!$B$14:$S$14"
End With

With .SeriesCollection.NewSeries

.Name = "='Scrabble'!$H$15"
.Values = "='Scrabble'!$B$18:$S$18"
End With

With .SeriesCollection.NewSeries

.Name = "='Scrabble'!$H$19"
.Values = "='Scrabble'!$B$22:$S$22"
End With
End With
End Sub

[p45cal]

border for a series object is a hidden member in xl2007, so it's probably going to be deprecated. I found the following in the past with quite a lot of difficulty, you can replace the .border line with (say):
.Format.Line.Visible = msoCTrue
.Format.Line.ForeColor.RGB = vbBlueit doesn't have to be vbBlue of course, you can use any mix of Red/Green/Blue:
.Format.Line.ForeColor.RGB = RGB(123, 123, 123) 'grey

[craigwg]

Thank you, guys/gals! I noticed that it was "deprecated", at least if that means right clicking and choosing "Show Properties" showed nothing--not even options. That was what was killing me the most.

Thanks

[Bob Phillips]

It is not deprecated, and there is no suggestion at present that it will be.

[p45cal]

It is not deprecated, and there is no suggestion at present that it will be.(Been away for a week) Does this count as a suggestion?:

"Programmability: hidden and deprecated objects and properties

Description: Charts provide new formatting objects based on OfficeArt. The previous formatting objects and properties are hidden and deprecated. Hidden and deprecated objects and properties include the Border, Caption, Characters, Fill, Font, HorizontalAlignment, Interior, Orientation, ReadingOrder, Shadow, and VerticalAlignment properties, the ChartFillFormat and ChartColorFormat objects, Chart 3-D properties, and more.
Reason for change: Charts are now integrated with OfficeArt, so the object model also has changed to match OfficeArt.
Migration path: To access the formatting features, switch to the new objects and methods. The objects and methods that are listed are still available, but might be removed in a future version.

"

from here: http://technet.microsoft.com/en-us/library/cc179160%28office.12%29.aspx