Vba to change chart series color - Keep getting errors

[cacos]

Hi everyone! Help!


I'm struggling with the following code. It works fine until you open another workbook, then I get a run-time error saying this line can't be found

Set cht = ActiveSheet.ChartObjects("Chart 19").Chart


The full code is:

[pre]Sub ColorChart()

Dim cht As Chart

Set cht = ActiveSheet.ChartObjects("Chart 19").Chart
Set objSeries = cht.SeriesCollection(1)

If Range("Colorcode").Value = 1 Then
objSeries.Interior.Color = RGB(142, 180, 227)
objSeries.Border.Color = RGB(85, 142, 213)

End If

(And there are a couple of more IFs until it ends)

End Sub

[/pre]

The sub is called when a combobox changes.


Many thanks

 

[Luke M]

Every time the code runs, is the worksheet containing the chart already selected? If not, we need to change

ActiveSheet to something else, like Worksheets("MySheet")

Has the chart name changed? When you click on the chart, what appears in the name box? If it's not "Chart 19", you'll need to modify the code to suit.

 

[cacos]

Tried changing to Worksheets and it still doesn't work. And the chart name is always the same.


I can't get it to work it's really picking my brain.


Also: It's a private sub on the Sheet where the chart is (and not a module, though it doesn't work on a module either); it's set to run when a Combobox changes; there are many comboboxes on that sheet.


I don't know if any of that helps, or if there's another way to do it.


BTW: when I said "workbook" I meant when you open any other Excel file while using this one, that's when you get the error.

 

[Hui]

Cacos


Can you post your file for us to review?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook