Recorded macro does not work on chart object

Question

I recorded the following macro :

Sheets("Rejets Techniques TGC").Select
ActiveSheet.ChartObjects("Graphique 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(1).Values = "='Données'!$EU$68:$IJ$68"
ActiveChart.SeriesCollection(1).XValues = "='Données'!$EU$1:$IJ$1"

However when I try to lauch it I get this error (translated from french):

Execution error '-2147024809 (80070057)'
There is no element with this name

How can this be? if there was no graph named this way I wouldn't have been able to record it. (yes I'm running it from the good sheet)

Thanks.

Answer 1

Here's what it comes down to: Your chart is not an object on the sheet, it is the sheet.

So while you use ActiveSheet.ChartObjects("Graphique 1").Activate to start your code, there are no ChartObjects found in your sheet, because the sheet is the Chart. So here's how you get around it:

Dim CO As Variant
Set CO = ActiveSheet
CO.Axes(xlCategory).Select
CO.SeriesCollection(1).Values = "='Données'!$ET$68:$IJ$68"
CO.SeriesCollection(1).XValues = "='Données'!$ET$1:$IJ$1"

And this should work just fine. I noticed that when I looked at the chart tab, I couldn't get into any cells. This is not abnormal, but it is not the most common way (that I see) to create the chart. To verify, I added a watch on the ActiveSheet and saw that it was indeed a chart (of type Object/Graph2) with all the normal chart methods available to it.

From there, I just plugged in your code, converting to the CO variable (but yours should still work using ActiveSheet across the board), and ran with no errors.

---

As a side note, using ActiveSheet is not always effective, and it is generally better to explicitly call the sheet, i.e. Set CO = ThisWorkbook.Sheets("Rejets Techniques TGC")

Answer 2

1 - Check if the active sheet is the one that contaisn the chart. Or use the sheet name in code to run it from any sheet.

2 - Check if the good sheet contains the chart with exact "Graphique 1" name. Maybe there's an underline, like "Graphique_1", or no space "Graphique1"...