Excel 2007 Charting macro

P

PCF_man

I have macros in 2003, however they do not work in 2007. So I recorded a new
macro in Excel 2007 which inserts a chart into the active sheet. Works great
when you record it, however when you go to use the macro it hangs up on the
first line. Part of the macro is as follows:
ActiveSheet.Shapes.addchart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='52weeks'!$C$2:$C$54"
ActiveChart.SeriesCollection(1).Name = "='52weeks'!$C$1"
ActiveChart.SeriesCollection(1).XValues = "='52weeks'!$B$2:$B$54"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "='52weeks'!$D$2:$D$54"
ActiveChart.SeriesCollection(2).Name = "='52weeks'!$D$1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "='52weeks'!$E$2:$E$54"
ActiveChart.SeriesCollection(3).Name = "='52weeks'!$E$1"

The macro that is built in 2003 works great including the charting aspect.
In 2007 everything works except the charting. I have tried various ways of
inserting a chart in 2007 with a macro, but none of them work. Any ideas
would be greatly appreciated. Remember though that 2003 and 2007 are quite
different when it comes to charting and I'm getting the feeling that the when
it comes to recording the code it does not work the way it should.

PCF_man
 
P

PCF_man

PCF_man said:
I have macros in 2003, however they do not work in 2007. So I recorded a new
macro in Excel 2007 which inserts a chart into the active sheet. Works great
when you record it, however when you go to use the macro it hangs up on the
first line. Part of the macro is as follows:
ActiveSheet.Shapes.addchart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='52weeks'!$C$2:$C$54"
ActiveChart.SeriesCollection(1).Name = "='52weeks'!$C$1"
ActiveChart.SeriesCollection(1).XValues = "='52weeks'!$B$2:$B$54"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "='52weeks'!$D$2:$D$54"
ActiveChart.SeriesCollection(2).Name = "='52weeks'!$D$1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "='52weeks'!$E$2:$E$54"
ActiveChart.SeriesCollection(3).Name = "='52weeks'!$E$1"

The macro that is built in 2003 works great including the charting aspect.
In 2007 everything works except the charting. I have tried various ways of
inserting a chart in 2007 with a macro, but none of them work. Any ideas
would be greatly appreciated. Remember though that 2003 and 2007 are quite
different when it comes to charting and I'm getting the feeling that the when
it comes to recording the code it does not work the way it should.

PCF_man

Worked this out myself.

The problem is that Excel 2007 charting model works completely different
than in 2003. A simple macro as follows solved the problem

Sub addchart()
Dim WS As Worksheet
Dim Rng As Range
Set Rng = Range("A65")
Set WS = Worksheets("52weeks")
WS.Shapes.addchart(xlLineMarkers, _
Left:=WS.Range("M2").Left, _
Top:=WS.Range("M2").Top, _
Width:=WS.Range("M2:T2").Width, _
Height:=WS.Range("M2:M17").Height).Select
ActiveChart.SetSourceData Source:=Range("C2:E54")
ActiveChart.SeriesCollection(1).XValues = "=52weeks!$A$2:$A$54"
ActiveChart.SeriesCollection(1).Name = "=52weeks!$C$1"
ActiveChart.SeriesCollection(2).Name = "=52weeks!$D$1"
ActiveChart.SeriesCollection(3).Name = "=52weeks!$E$1"
ActiveChart.ApplyChartTemplate ("2007_52weeks")
ActiveChart.SetElement msoElementChartTitleAboveChart
ActiveChart.ChartTitle.Caption = Rng
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding a Chart with a macro in 2007 3
Bubble Chart 1
Floating Chart? 2
Help 0
#N/A isn't charting in VBA 2
No Data Lables 2
"Lines on 2 Axes" fails on Excel 2007 1
How to change the row number in macro code? 1

Top