How do I make runtime color changes to chart?

[Mark Llewellyn]

Using VB6 and Office 97...

I am having no luck changing the fill, border, etc. colors of the bars
in an Excel Chart from its host VB app. I am calling up an instance
of an Excel worksheet, loading an existing sheet with chart then
manipulating the values, ranges, etc. Our broad range of users
require an easy means to select various colors at runtime.

I've been through the VBAXL8.HLP file several times but haven't found
any examples (that work). I have attempted setting colors for a
SeriesCollection object, but I am getting errors related to them being
read-only properties.

Can anyone help out?

Mark Llewellyn
Xfour...@Xhotmail.com
remove X's for email replies

[Brian Reilly, MS MVP]

Mark,
You have to use the indexed values of the 56 available colors. But the
catch is they aren't in order of rows and columns. If you need the
list in an order that makes sense let me know and I'll post them here
again.

Here's a sample piece of code to get you going.

With ActiveChart.SeriesCollection(1)
With .Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
.Fill.OneColorGradient Style:=msoGradientDiagonalDown,
Variant:=4 _
, Degree:=0.231372549019608
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 22
End With

Brian Reilly, MS MVP

Brian Reilly, MS MVP
http://www.reillyand.com
Reillyand, Inc.

[Mark Llewellyn]

Thanks for the sample Brian. (And "Yes, Please!" on the color list)

Mark

On Tue, 03 Apr 2001 13:25:39 GMT, br...@reillyand.com (Brian Reilly,
MS MVP) wrote:

> Mark,
> You have to use the indexed values of the 56 available colors. But the
> catch is they aren't in order of rows and columns. If you need the
> list in an order that makes sense let me know and I'll post them here
> again.

Mark Llewellyn

[David Lee]

The following lines will set the border and fill of the first bar in a
selected chart to red.

ActiveChart.SeriesCollection(1).Border.ColorIndex=3
ActiveChart.SeriesCollection(1).Interior.ColorIndex=3

or you can select the chart from code eg:

ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Interior.ColorIndex=3

You can also set colours using the color property eg .Color = RGB(0, 255,
0)

HTH

David

Mark Llewellyn <Xfour...@Xhotmail.com> wrote in article
<i3gjctgpi069n7rok...@4ax.com>...

[Brian Reilly, MS MVP]

David, While your first two comments are correct the following is not
correct.

>You can also set colours using the color property eg .Color = RGB(0, 255,
>0)

This just sets the color property to the value in the ColorIndex group
to the closest value to that RGB value,
The following code demonstrates that and it also serves to "re-order"
the color indexes that Excel uses to be in the order of rows and
columns which Excel does not use in any logical order, probably going
back to the days of 16 colors.

Note: the following will give you mostly "Black" variations to show
that a line like
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Interior.Color =
RGB(0,255,0) 'which should be green
doesn't even look like a green but is actually one of the color
indexes. In the following code, you can change the RGB values to what
you like so that you can create your own palette, or just use it as a
reference to what row and what column in the form's grid is actually
being referred to by the colorIndex number.

Sub Create_Excel_Palette_By_RGB()
'Developer: Brian Reilly
'Purpose: Assigns the RGB value to each of 56 colors available to
Excel.
'Special Notes: The Excel index values are out of order in Excel
97 and 2000.
'The index values as follows work row by row from the top and left
to right within each row.

'The first row from left to right
ActiveWorkbook.Colors(1) = RGB(11, 11, 11) 'swatch 1
from left
ActiveWorkbook.Colors(53) = RGB(12, 12, 12) 'swatch 2
from left
ActiveWorkbook.Colors(52) = RGB(13, 13, 13) 'swatch 3
from left
ActiveWorkbook.Colors(51) = RGB(14, 14, 14) 'swatch 4
from left
ActiveWorkbook.Colors(49) = RGB(15, 15, 15) 'swatch 5
from left
ActiveWorkbook.Colors(11) = RGB(16, 16, 16) 'swatch 6
from left
ActiveWorkbook.Colors(55) = RGB(17, 17, 17) 'swatch 7
from left
ActiveWorkbook.Colors(56) = RGB(18, 18, 18) 'swatch 8
from left

'The second row from left to right
ActiveWorkbook.Colors(9) = RGB(21, 21, 21) 'swatch 1
from left
ActiveWorkbook.Colors(46) = RGB(22, 22, 22) 'swatch 2
from left
ActiveWorkbook.Colors(12) = RGB(23, 23, 23) 'swatch 3
from left
ActiveWorkbook.Colors(10) = RGB(24, 24, 24) 'swatch 4
from left
ActiveWorkbook.Colors(14) = RGB(25, 25, 25) 'swatch 5
from left
ActiveWorkbook.Colors(5) = RGB(26, 26, 26) 'swatch 6
from left
ActiveWorkbook.Colors(47) = RGB(27, 27, 27) 'swatch 7
from left
ActiveWorkbook.Colors(16) = RGB(28, 28, 28) 'swatch 8
from left

'The third row
ActiveWorkbook.Colors(3) = RGB(31, 31, 31) 'swatch 1
from left
ActiveWorkbook.Colors(45) = RGB(32, 32, 32) 'swatch 2
from left
ActiveWorkbook.Colors(43) = RGB(33, 33, 33) 'swatch 3
from left
ActiveWorkbook.Colors(50) = RGB(34, 34, 34) 'swatch 4
from left
ActiveWorkbook.Colors(42) = RGB(35, 35, 35) 'swatch 5
from left
ActiveWorkbook.Colors(41) = RGB(36, 36, 36) 'swatch 6
from left
ActiveWorkbook.Colors(13) = RGB(37, 37, 37) 'swatch 7
from left
ActiveWorkbook.Colors(48) = RGB(38, 38, 38) 'swatch 8
from left

'The fourth row
ActiveWorkbook.Colors(7) = RGB(41, 41, 41) 'swatch 1
from left
ActiveWorkbook.Colors(44) = RGB(42, 42, 42) 'swatch 2
from left
ActiveWorkbook.Colors(6) = RGB(43, 43, 43) 'swatch 3
from left
ActiveWorkbook.Colors(4) = RGB(44, 44, 44) 'swatch 4
from left
ActiveWorkbook.Colors(8) = RGB(45, 45, 45) 'swatch 5
from left
ActiveWorkbook.Colors(33) = RGB(46, 46, 46) 'swatch 6
from left
ActiveWorkbook.Colors(54) = RGB(47, 47, 47) 'swatch 7
from left
ActiveWorkbook.Colors(15) = RGB(48, 48, 48) 'swatch 8
from left

'The fifth row
ActiveWorkbook.Colors(38) = RGB(51, 51, 51) 'swatch 1
from left
ActiveWorkbook.Colors(40) = RGB(52, 52, 52) 'swatch 2
from left
ActiveWorkbook.Colors(36) = RGB(53, 53, 53) 'swatch 3
from left
ActiveWorkbook.Colors(35) = RGB(54, 54, 54) 'swatch 4
from left
ActiveWorkbook.Colors(34) = RGB(55, 55, 55) 'swatch 5
from left
ActiveWorkbook.Colors(37) = RGB(56, 56, 56) 'swatch 6
from left
ActiveWorkbook.Colors(39) = RGB(57, 57, 57) 'swatch 7
from left
ActiveWorkbook.Colors(2) = RGB(58, 58, 58) 'swatch 8
from left

'The sixth row The first default row for charts
ActiveWorkbook.Colors(17) = RGB(61, 61, 61) 'swatch 1
from left
ActiveWorkbook.Colors(18) = RGB(62, 62, 62) 'swatch 2
from left
ActiveWorkbook.Colors(19) = RGB(63, 63, 63) 'swatch 3
from left
ActiveWorkbook.Colors(20) = RGB(64, 64, 64) 'swatch 4
from left
ActiveWorkbook.Colors(21) = RGB(65, 65, 65) 'swatch 5
from left
ActiveWorkbook.Colors(22) = RGB(66, 66, 66) 'swatch 6
from left
ActiveWorkbook.Colors(23) = RGB(67, 67, 67) 'swatch 7
from left
ActiveWorkbook.Colors(24) = RGB(68, 68, 68) 'swatch 8
from left

'The seventh row. The second default row for charts
ActiveWorkbook.Colors(25) = RGB(71, 71, 71) 'swatch 1
from left
ActiveWorkbook.Colors(26) = RGB(72, 72, 72) 'swatch 2
from left
ActiveWorkbook.Colors(27) = RGB(73, 73, 73) 'swatch 3
from left
ActiveWorkbook.Colors(28) = RGB(74, 74, 74) 'swatch 4
from left
ActiveWorkbook.Colors(29) = RGB(75, 75, 75) 'swatch 5
from left
ActiveWorkbook.Colors(30) = RGB(76, 76, 76) 'swatch 6
from left
ActiveWorkbook.Colors(31) = RGB(77, 77, 77) 'swatch 7
from left
ActiveWorkbook.Colors(32) = RGB(78, 78, 78) 'swatch 8
from left

End Sub

Brian Reilly, MS MVP
On 3 Apr 2001 14:40:12 GMT, "David Lee" <dlee_m...@hotmail.com>
wrote:

Brian Reilly, MS MVP
http://www.reillyand.com
Reillyand, Inc.

[Brian Reilly, MS MVP]

Mark, See code in next message from me here.
Brian Reilly, MS MVP

Brian Reilly, MS MVP
http://www.reillyand.com
Reillyand, Inc.