0

Very new to Excel VBA here... Very similar to this post: Avoiding the use of Activate and Select when working with charts (Excel), I am attempting to avoid using Activate in order to make a small change to a number of Charts in a large workbook. I am performing this task for a good number of charts, so I would like to minimize the run-time as much as possible and have heard that the select/activate functions tend to slow down macros.

My example code looks like this:

ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.FullSeriesCollection(1).XValues = "=Leb!$C$2:$G$2"

I have attempted to replace these two lines with the following since this makes logical sense to me:

ActiveSheet.ChartObjects("Chart 3").FullSeriesCollection(1).XValues = "=Leb!$C$2:$G$2"

This code causes an error when run, so I then attempted to use the WITH format shown by the post mentioned above:

With Worksheets("Chart").ChartObjects("Chart 3").Chart.XValues = "=Leb!$C$2:$G$2"
End With

This also throws an error.

I understand that I am probably making a small, syntax error, but would anyone be able to provide any insight into what I am doing wrong? (I have never used the WITH function before, so some general insight on it would be very helpful as well.)

Thanks in advance.

Community
  • 1
  • 1
SpippyTheInsane
  • 25
  • 1
  • 7
  • You need to include the keyword _Chart_ between _ChartObjects_ and _FullSeriesCollection_: `ActiveSheet.ChartObjects("Chart 3").Chart.FullSeriesCollection(1).XValues = "=Leb!$C$2:$G$2"` – Jon Peltier Feb 09 '17 at 20:17

1 Answers1

3

Almost there...

With Worksheets("Chart").ChartObjects("Chart 3").Chart
    .SeriesCollection(1).XValues = "=Leb!$C$2:$G$2"
End With

XValues belongs to a Series object, not the Chart (referring to your last code sample).

EDIT:

Dim arrCharts, cht
arrCharts = Array("Chart 3", "Chart 4", "Chart 5", "Chart 7")

For Each cht In arrCharts
    With Worksheets("Chart").ChartObjects(cht).Chart
        .SeriesCollection(1).XValues = "=Leb!$C$2:$G$2"
    End With
Next cht
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thank you! Is it also possible to make this exact change to multiple charts on the same sheet? I tried `With Worksheets("Dash Board").ChartObjects("Chart 3", "Chart 4", "Chart 5", "Chart 7").Chart.SeriesCollection(1).XValues = "=Leb!$C$2:$G$2"End With` – SpippyTheInsane Feb 09 '17 at 20:11
  • @TimWilliams You mean `SeriesCollection` belongs to a `Chart`, not to the `ChartObject` – Jon Peltier Feb 09 '17 at 20:23
  • @Tim Williams Thanks again! I had to change the 2nd "End With" to "Next cht", but it works perfectly now :) – SpippyTheInsane Feb 09 '17 at 20:27