macro for dynamic chart

[CMBond]

I am trying to create a macro that will automatically plot data. This is the code I am using so far:

Dim Numsheets As Integer
Dim Datx1l, Daty1 As String
Dim Datx, Daty As String

Numsheets = ActiveWorkbook.Sheets.Count

Datx1 = "DatCol" & Sheets("Graphs").OLEObjects("ComboBox1").Object.ListIndex + 1 & "_" & 1
Daty1 = "DatCol" & Sheets("Graphs").OLEObjects("ComboBox2").Object.ListIndex + 1 & "_" & 1

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.Location Where:=xlLocationAsObject, Name:="Graphs"
With ActiveChart
.SeriesCollection(1).XValues = Datx1
.SeriesCollection(1).Values = Daty1
.SeriesCollection(1).Name = Worksheets(1).Name

For j = 2 To Numsheets - 1
Worksheets(j).Activate

Datx = "DatCol" & Sheets("Graphs").OLEObjects("ComboBox1").Object.ListIndex + 1 & "_" & j
Daty = "DatCol" & Sheets("Graphs").OLEObjects("ComboBox2").Object.ListIndex + 1 & "_" & j

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(j).XValues = Datx
ActiveChart.SeriesCollection(j).Name = Worksheets(j).Name
'ActiveChart.SeriesCollection.NewSeries.Formula = "=SERIES(" & ActiveSheet.Name & ",'" & ActiveWorkbook.Name & "'!Datx,'" & ActiveWorkbook.Name & "'!Daty," & j & ")"
Debug.Print "=SERIES(" & ActiveSheet.Name & ",'" & ActiveWorkbook.Name & "'!Datx,'" & ActiveWorkbook.Name & "'!Daty," & j & ")"
Next
End With

When I run the macro I get an error stating Method 'SeriesCollection' of object'_Chart' failed on the bold line. I have been using other online examples as references and I am not sure why this is not working. Please help!

 

[KenMillard]

Datx1 appears to be a string. If you make it a range, that should work.

Something like

set Datx1 = thisworkbook.worksheets("Sheet1").Range("A1:A143")

Should work

Or, if the range is on the active sheet you could use

Set Datx1 = Range("A1:A143")

 

[CMBond]

OK making Datx1 and Daty1 fixed that problem and the first series will plot, but now a error occurs when I try to add the next series through the loop. I made Datx and Daty ranges as well, but my error occurs on the line

ActiveChart.SeriesCollection.NewSeries

saying Error 91, Object variable or With block variable not set. I don't understand why it is doing this, any suggestions? The code now looks like this:

Dim j As Integer
Dim Numsheets As Integer
Dim Datx1, Daty1 As Range
Dim Datx, Daty As Range

Numsheets = ActiveWorkbook.Sheets.Count

Set Datx1 = Range("DatCol" & Sheets("Graphs").OLEObjects("ComboBox1").Object.ListIndex + 1 & "_" & 1)
Set Daty1 = Range("DatCol" & Sheets("Graphs").OLEObjects("ComboBox2").Object.ListIndex + 1 & "_" & 1)

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.Location Where:=xlLocationAsObject, Name:="Graphs"
With ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = Worksheets(1).Name
ActiveChart.SeriesCollection(1).XValues = Datx1
ActiveChart.SeriesCollection(1).Values = Daty1


For j = 2 To Numsheets - 1
Worksheets(j).Activate

Set Datx = Range("DatCol" & Sheets("Graphs").OLEObjects("ComboBox1").Object.ListIndex + 1 & "_" & j)
Set Daty = Range("DatCol" & Sheets("Graphs").OLEObjects("ComboBox2").Object.ListIndex + 1 & "_" & j)

With ActiveChart
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(j).XValues = Datx
ActiveChart.SeriesCollection(j).Values = Daty
ActiveChart.SeriesCollection(j).Name = Worksheets(j).Name
End With

Next
End With

 

[brian.wethington]

I am unsure of how your data is set up for the charts but it may be easier to set up dynamicailly named ranges to update the graph. Just a thought.

 

[Jon Peltier]

You've activated another worksheet, so there's no active chart.

It's always better not to activate or select objects in your procedures. Change this:

Worksheets(j).Activate 

Set Datx = Range("DatCol" & Sheets("Graphs").OLEObjects("ComboBox1").Object.ListIndex + 1 & "_" & j) 
Set Daty = Range("DatCol" & Sheets("Graphs").OLEObjects("ComboBox2").Object.ListIndex + 1 & "_" & j)

to this:

Set Datx = Worksheets(j).Range("DatCol" & Sheets("Graphs").OLEObjects("ComboBox1").Object.ListIndex + 1 & "_" & j) 
Set Daty = Worksheets(j).Range("DatCol" & Sheets("Graphs").OLEObjects("ComboBox2").Object.ListIndex + 1 & "_" & j)

You should also try to avoid using an active chart, but set a chart variable to the active chart here:

ActiveChart.Location Where:=xlLocationAsObject, Name:="Graphs" 
Dim cht As Chart
Set cht = ActiveChart

Then instead of ActiveChart, refer to cht.

 

[CMBond]

thanks so much for the help... it's finally working!