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" wrote:
> 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" wrote:
>
> > 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
> >
> >
|
Similar Threads
