Updating Radar Chart 'X-Axis' equivalent using VBA

[DaveCPhillips]

HI,

I have been working for a while on an spreadsheet that has a number of charts that are updated using a VBA script that is used to either enter data manually or extract from a data file. Most of these charts have no problem and the updates occur correctly. However one is a Radar chart and this is the one giving me problems.
Part of the routine has the option to add in additional x-Axis items to the charts. When this option is activated the source data for the chart is updated correctly and the actual graph is pointed to the newly increased source data, however when you then check the display, the new x-Axis caption is missing.

These charts are tracking the progress of software sprints, and the data corresponds to Epic names and the % Complete from this Sprint, and % complete in Last sprint. The code allows you to add another Epic.

The code I use to update the chart is as follows:-

With ActiveSheet
.ChartObjects("Radar").Activate
With ActiveChart
.SeriesCollection(1).Select
.SeriesCollection(1).XValues = "='Data For Graphs'!R4C7:R4C" & ctMax ' New Titles range"
.SeriesCollection(1).Values = "='Data For Graphs'!R7C7:R7C" & ctMax ' new Total % range
.SeriesCollection(2).Values = "='Data For Graphs'!R9C7:R9C" & ctMax ' New Current % range
.SeriesCollection(3).Values = "='Data For Graphs'!R8C7:R8C" & ctMax ' New Last % range
End With
End With

The chart has updated and shows the new entry - but the x-axis label with the new Epic name is not present.

Anyone got any ideas? If I changed the Chart type to a standard bar chart then this works fine and the label does update.

 

[Steve=True]

Hi Dave,

New to VBA, but your line

.SeriesCollection(1).XValues = "='Data For Graphs'!R4C7:R4C" & ctMax ' New Titles range"

has a " at the end of it,

Perhaps that is the problem?

Steve=True

 

[DaveCPhillips]

Hi Dave,

New to VBA, but your line

.SeriesCollection(1).XValues = "='Data For Graphs'!R4C7:R4C" & ctMax ' New Titles range"

has a " at the end of it,

Perhaps that is the problem?

Steve=True

Hi Steve,

Thanks for the response, just checked my original code and the " isn't there, so not sure how it got into here when I copied it across - so it is not that.

Cheers,
Dave