Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a Chart with a macro in 2007
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a Chart with a macro in 2007
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(Whe=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a Chart with a macro in 2007
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(Whe=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a Chart with a macro in 2007
Does it give any particular error message or just hang?
"PCF_man" wrote: 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(Whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding commands to the Excel 2007 Chart context menu | Charts and Charting in Excel | |||
Problem adding chart (Office 2007) | Charts and Charting in Excel | |||
Adding Extra Data Row to Chart via Macro | Excel Programming | |||
Adding data series to chart via macro | Charts and Charting in Excel | |||
Adding chart using a macro | Excel Programming |