bhallissey
07-10-2009, 04:26 PM
I want to use VBA code to dynamically set the values in an embedded line chart. I have tried several approaches and can not get the series to change.
Approach #1
Code to change an existing line series produces Run-time error '1004': Unable to change the Values property of the Series Class
objSht.ChartObjects(1).Activate
ActiveChart.SeriesCollection(1).Values = resSht.Range("d4:bk4")
Approach #2
Code to add a new series and set the series label produces Run-time error '1004': Unable to set the Name property of the Series Class
objSht.ChartObjects(1).Activate
ActiveChart.SeriesCollection.Add Source:=resSht.Range("$d$" & CStr(resRow) & ":$bk$" & CStr(resRow)), SeriesLabels:=False, Categorylabels:=False
ActiveChart.Refresh
ActiveChart.SeriesCollection(1).Name = "=""abc"""
With Approach #2, I also get Run-time error '1004': Delete method of Series class failed.
objSht.ChartObjects(1).Activate
While ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(1).Delete
Wend
I am at a complete loss! Please help.
Approach #1
Code to change an existing line series produces Run-time error '1004': Unable to change the Values property of the Series Class
objSht.ChartObjects(1).Activate
ActiveChart.SeriesCollection(1).Values = resSht.Range("d4:bk4")
Approach #2
Code to add a new series and set the series label produces Run-time error '1004': Unable to set the Name property of the Series Class
objSht.ChartObjects(1).Activate
ActiveChart.SeriesCollection.Add Source:=resSht.Range("$d$" & CStr(resRow) & ":$bk$" & CStr(resRow)), SeriesLabels:=False, Categorylabels:=False
ActiveChart.Refresh
ActiveChart.SeriesCollection(1).Name = "=""abc"""
With Approach #2, I also get Run-time error '1004': Delete method of Series class failed.
objSht.ChartObjects(1).Activate
While ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(1).Delete
Wend
I am at a complete loss! Please help.