macro for dynamic chart

CMBond

New Member
Joined
Jul 13, 2007
Messages
15
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!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

KenMillard

Board Regular
Joined
Apr 25, 2007
Messages
151
Datx1 appears to be a string. If you make it a range, that should work.

Something like

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

Should work

Or, if the range is on the active sheet you could use
Code:
Set Datx1 = Range("A1:A143")
 

CMBond

New Member
Joined
Jul 13, 2007
Messages
15
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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

MrExcel MVP
Joined
May 14, 2003
Messages
5,269
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

Code:
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:

Code:
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:

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

Then instead of ActiveChart, refer to cht.
 

Forum statistics

Threads
1,181,410
Messages
5,929,774
Members
436,688
Latest member
sunnyBNH013

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top