Thread: Setting Series Values In VBA

Good morning everyone,

I am in the middle of building a new navigation portal for my stakeholders and have some charts that update based upon the selection from a drop down list on the worksheet. The problem i have is that it keeps randomly falling over for no aparrent reason. The message i get is "Unable to set the values property of the series class". This has happened at various times at different points in the code. Currently my development version works fine but my testing version is falling over. Below is a section of the code i run as an update macro on the drop down list. The original code i wrote was much more streamlined but as it keeps falling over ive changed it to get it working.

One other point to mention is that it only ever fails when updating the 2nd data series.

Any help would be greatly appreciated.

Thanks

EddiesVoicebox

Code:

Private Sub cmboMetric_Change()
    Dim ActColRef, FCColRef As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ActiveSheet.Shapes("Picture 506").Visible = True

If Range("AL4").Value = "Total Calls" Then
                                                        ActColRef = "2"
                                                        FCColRef = "3"
'CAR CHART
                                                        ActiveSheet.ChartObjects("Chart 498").Activate
                                                        ActiveChart.PlotArea.Select
                                                        ActiveChart.SeriesCollection(1).Values = "=dataPRVCAR!R6C" & ActColRef & ": R58C" & ActColRef
    If Range("X4").Value = "0+12" Then ActiveChart.SeriesCollection(2).Values = "=dataPRVCAR!R814C" & FCColRef & ": R866C" & FCColRef
    If Range("X4").Value = "5+7" Then ActiveChart.SeriesCollection(2).Values = "=dataPRVCAR!R867C" & FCColRef & ": R919C" & FCColRef
                                                        ActiveWindow.Visible = False
'HOMECHART
                                                        ActiveSheet.ChartObjects("Chart 499").Activate
                                                        ActiveChart.PlotArea.Select
                                                        ActiveChart.SeriesCollection(1).Values = "=dataPRVHOME!R6C" & ActColRef & ": R58C" & ActColRef
    If Range("X4").Value = "0+12" Then ActiveChart.SeriesCollection(2).Values = "=dataPRVHOME!R814C" & FCColRef & ": R866C" & FCColRef
    If Range("X4").Value = "5+7" Then ActiveChart.SeriesCollection(2).Values = "=dataPRVHOME!R867C" & FCColRef & ": R919C" & FCColRef
                                                        ActiveWindow.Visible = False

Hi Andy,

Thanks for getting back to me.

The charts are just standard run of the mill line charts with 2 data series. The data that feeds the charts contains no errors at all, there are some zero values but these are shown in the charts ok when it works (as i would expect them to do).

The charts show forecast vs actual performance based upon different metrics (calls, quotes, sales etc) which is why i need to be able to change the data series when the user selects a different cut of data.

Thanks

EddiesVoicebox