Graph colors in excel

th3_pu7p · Mar 20th, 2002, 04:30 AM

I was wondering if you were able to help me find out how to change the color of a graphs bar in Excel. I am using Progress (DataBase software) to populate the worksheet and then using another call to create a chart form the data in the worksheet.

I have tried using the ColorIndex to change the color of the graph, but failed, any ideas?

Dave Hawley · Mar 20th, 2002, 05:01 AM

Hi Th3_Pu7P

Did you try recording a macro and studying the code?

th3_pu7p · Mar 20th, 2002, 05:07 AM

I cannot use a macro as I am using an external program. (i.e. Progress). This is what I have:

chChart = chExcelApplication:ActiveChart:ChartWizard(chWorksheet3Range, 2, 3, 2, 1, 0, FALSE, "Sales Analysis by Salesman","Saleman" ,"Litreage" ).

WHERE chChart is the handle that you use in Progress. The above code creates the table.
I need to do something like this below.
chChart = chExcelApplication:ActiveChart:Chart:ColorIndex = 2.

th3_pu7p · Mar 20th, 2002, 05:09 AM

I have compiled the marco and studied the code. It uses the Interior call, but for some reason Progress does not support it
i.e:

With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid

Dave Hawley · Mar 20th, 2002, 05:26 AM

I am not suggesting you use the recorded macro I'm suggesting you use the code generated to work it out.

ActiveChart.SeriesCollection(1).Interior.ColorIndex = 27

Works just fine if the chart is active.

th3_pu7p · Mar 20th, 2002, 05:43 AM

Dave,
Yes thanks, I have been looking at the MSDN page and the information for that is there also. I was previously getting errors in Excel when I was making that call, but have fixed that, now the colours are simply not changing. Wierd.

Dave Hawley · Mar 20th, 2002, 05:47 AM

How did you stop the errors

On error resume next ?

If so this will only stop the code breaking and you MUST set it back with

On Error GoTo 0

Otherwise you will never get any debugs.

th3_pu7p · Mar 20th, 2002, 06:02 AM

No, it was communication calls between Progress and Excel. This is the code I have for the colour change, but it is not changing the colour of the bar. I have activated the correct chart.
chChart = chExcelApplication:ActiveChart:SeriesCollection(1):Interior:ColorIndex = 3.
Any ideas?

Dave Hawley · Mar 20th, 2002, 06:09 AM

what is the

"chChart =" at the start for?

th3_pu7p · Mar 20th, 2002, 06:10 AM

chChart is the handle that Progress uses to call the functions on this Excel Graph.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 04:30 AM	#1
th3_pu7p New Member Join Date: Mar 2002 Posts: 7	I was wondering if you were able to help me find out how to change the color of a graphs bar in Excel. I am using Progress (DataBase software) to populate the worksheet and then using another call to create a chart form the data in the worksheet. I have tried using the ColorIndex to change the color of the graph, but failed, any ideas?

Mar 20th, 2002, 05:01 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Th3_Pu7P Did you try recording a macro and studying the code?

Mar 20th, 2002, 05:07 AM	#3
th3_pu7p New Member Join Date: Mar 2002 Posts: 7	I cannot use a macro as I am using an external program. (i.e. Progress). This is what I have: chChart = chExcelApplication:ActiveChart:ChartWizard(chWorksheet3Range, 2, 3, 2, 1, 0, FALSE, "Sales Analysis by Salesman","Saleman" ,"Litreage" ). WHERE chChart is the handle that you use in Progress. The above code creates the table. I need to do something like this below. chChart = chExcelApplication:ActiveChart:Chart:ColorIndex = 2.

Mar 20th, 2002, 05:09 AM	#4
th3_pu7p New Member Join Date: Mar 2002 Posts: 7	I have compiled the marco and studied the code. It uses the Interior call, but for some reason Progress does not support it i.e: With Selection.Interior .ColorIndex = 1 .Pattern = xlSolid

Mar 20th, 2002, 05:26 AM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	I am not suggesting you use the recorded macro I'm suggesting you use the code generated to work it out. ActiveChart.SeriesCollection(1).Interior.ColorIndex = 27 Works just fine if the chart is active.

Mar 20th, 2002, 05:43 AM	#6
th3_pu7p New Member Join Date: Mar 2002 Posts: 7	Dave, Yes thanks, I have been looking at the MSDN page and the information for that is there also. I was previously getting errors in Excel when I was making that call, but have fixed that, now the colours are simply not changing. Wierd.

Mar 20th, 2002, 05:47 AM	#7
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	How did you stop the errors On error resume next ? If so this will only stop the code breaking and you MUST set it back with On Error GoTo 0 Otherwise you will never get any debugs.

Mar 20th, 2002, 06:02 AM	#8
th3_pu7p New Member Join Date: Mar 2002 Posts: 7	No, it was communication calls between Progress and Excel. This is the code I have for the colour change, but it is not changing the colour of the bar. I have activated the correct chart. chChart = chExcelApplication:ActiveChart:SeriesCollection(1):Interior:ColorIndex = 3. Any ideas?

Mar 20th, 2002, 06:09 AM	#9
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	what is the "chChart =" at the start for?

Mar 20th, 2002, 06:10 AM	#10
th3_pu7p New Member Join Date: Mar 2002 Posts: 7	chChart is the handle that Progress uses to call the functions on this Excel Graph.