Problem with using a macro to create a chart
 

Hi,

I am struggling with how to create a new graph in my spreadsheet and
place it as an object in an existing sheet. The code i have (mainly
salvaged from using the record function) fails on the highlighted line
below. Even replaying the recorded macros dont help as they still fail
at this line.

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Worksheets(1).Range("A1")
ActiveChart.Location Whe=xlLocationAsObject, _
Name:="GIFcontainer"

It seems that as soon as the first line is executed a new sheet is
automatically created containing the new graph hence messing up the
last line.


Does anyone know whats going on?

Many Thanks,
Tom


---
Message posted from http://www.ExcelForum.com/

Problem with using a macro to create a chart
 

Tom -

In the text message propagated to the msnews server, I don't see any
highlighted lines, so I can only guess what line throws the error. But
my philosophy is, if you want the chart in the worksheet, build it there
to begin with. The recorder is very helpful, but it will never show you
this.

Use this syntax:

dim myChart as ChartObject
Set myChart = Worksheets("GIFcontainer").ChartObjects.Add _
(MyLeft, MyTop, MyWidth, MyHeight)
With myChart.Chart
.SetSourceData Source:=Worksheets(1).Range("A1")
' !! ONLY ONE CELL??
' etc.
End With

MyLeft, MyTop are dimensions in points.

Check on line help or my web site for more information:

http://peltiertech.com/Excel/Charts/chartvba.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

lopsided < wrote:

Hi,

I am struggling with how to create a new graph in my spreadsheet and
place it as an object in an existing sheet. The code i have (mainly
salvaged from using the record function) fails on the highlighted line
below. Even replaying the recorded macros dont help as they still fail
at this line.

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Worksheets(1).Range("A1")
ActiveChart.Location Whe=xlLocationAsObject, _
Name:="GIFcontainer"

It seems that as soon as the first line is executed a new sheet is
automatically created containing the new graph hence messing up the
last line.


Does anyone know whats going on?

Many Thanks,
Tom


---
Message posted from http://www.ExcelForum.com/

Problem with using a macro to create a chart
 

sorry Jon, the highlighted line should have been

ActiveChart.Location Whe=xlLocationAsObject

I agree it would be best to put the graph in the worksheet first, but
the macro is such that it will create a new report worksheet which
needs to create graphs on it.

But your code worked perfectly and better than I had expected as you
can use the myLeft etc parameters to position the graph exactly.

Many Thanks,
Tom


---
Message posted from http://www.ExcelForum.com/