Adding a Chart with a macro in 2007

[PCF_man]

I have data in a sheet that was built with a macro. In order to enhance this
data I want to chart it. I recorded the macro to chart it and it is included
below. The problem is when I run the recorded macro it hangs up on the
"ActiveSheet.Shapes.addchart.Select" . All this is the recorded macro.
Can't seem to get around it.

Recorded Macro.

Range("B1:E54").Select
ActiveSheet.Shapes.addchart.Select
ActiveChart.SetSourceData Source:=Range("'52weeks'!$B$1:$E$54")
ActiveChart.ChartType = x1LineMarkers

When I record this macro it works perfectly. When I go to run it again it
hangs up

HELP

 

[Michael Hudston]

I find its not as simple as just using the recorded info. you need to be
more specific.

I created charts like this

Sub Chrt_Current_Status_Click()

Dim chtChart As Chart

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject,
Name:="sheetname")

With chtChart

.ChartType = xlCylinderColClustered ' Chart type

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("G34:H39") '
source of data
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$G$34:$G$39"
'.Axes(xlCategory, xlPrimary) = True

' The Parent property is used to set properties of the Chart. ' sets
size of chart

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R34").Width
.Height = Range("G3:R34").Height

End With

End With


End Sub

 

[PCF_man]

I have a macro that works great in Excel 2003 and it is somewhat similar to
the one you have shown, however, this hangs up as well in Excel 2007. I fear
it has something to do with the way 2007 adds charts. Your macro hung up at
the Set chtChart = Chart.Add stage. I am getting to the point that I can't
use 2007 to chart my data unless I do it all manually, which when you have to
do it over and over again becomes a pain. I have to use 2003 to chart the
data.

If you ever find a solution to this in 2007, I sure would appreciate the
answer.

Just for your info the macro that I use in 2003 is written by someone else
and works great except in 2007. It is as follows:

Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long

If TypeName(Selection) <> "Range" Then Exit Sub
Range("B1:E54").Select
Set rngChtData = Selection
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart
.ChartType = xlLineMarkers

Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
For iColumn = 2 To rngChtData.Columns.Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
Next
myChtObj.Activate
End With
Call FormatChart
End Sub

 

[Michael Hudston]

Does it give any particular error message or just hang?

 

[David Calder]

I having the same problem

When I record the macro it works fine. When I play it back it has this error:

Run-time error '1004':

Application-defined or object defined error

Here is the code:

Sheets("Summary").Select
Range("B7:B10,E7:E10").Select
Range("E7").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range( _
"Summary!$B$7:$B$10;Summary!$E$7:$E$10")
ActiveChart.ChartType = xlPie
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Text = "User Activity"
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select

Does anyone have any idea how to solve this?

Thank you!