Place chart at specific cell location
 

I would like to find a method to place a chart at a specific cell location
within a worksheet. I am adding a chart using data within the spreadsheet
and need to place the chart next to the data. I can't seem to find a method.

The code I am using to add the sheet is as follows and I would like to place
the top left hand corner in cell J2.

Thanks
BLandmine

sName = ActiveSheet.Name
Set sh = ActiveSheet
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=sh.Range("B3:C12"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:=sName
ActiveChart.HasLegend = False

Place chart at specific cell location
 

Hi
When the chart is in a Worksheet you have to move the Chart container
(PArent) which is a ChartObject object. So, say the chart is the #1 on the
active sheet:
'---------------------------------------
Sub test()
With ActiveSheet
.ChartObjects(1).Top = .Range("B2").Top
.ChartObjects(1).Left = .Range("B2").Left
End With
End Sub
'---------------------------------------

Now based on the ActiveChart, you would do something like
'-----------------------------------------------------
Sub test2()
With ActiveChart.Parent 'ie With ChartObject
.Top = .PArent.Range("B2").Top ' ie ActiveChart.Parent.Parent=Worksheet
.Left = .PArent.Range("B2").Left
End With
End Sub
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Landmine" wrote:

I would like to find a method to place a chart at a specific cell location
within a worksheet. I am adding a chart using data within the spreadsheet
and need to place the chart next to the data. I can't seem to find a method.

The code I am using to add the sheet is as follows and I would like to place
the top left hand corner in cell J2.

Thanks
BLandmine

sName = ActiveSheet.Name
Set sh = ActiveSheet
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=sh.Range("B3:C12"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:=sName
ActiveChart.HasLegend = False

Place chart at specific cell location
 

You've used Charts.Add to create the chart (most probably because that's the
code that the macro recorder spits out). If you use ChartObjects.Add
instead, you get the opportunity to specify the chartobject's size and
position as you create it.

eg.

With ActiveSheet.ChartObjects.Add(Left:=Range("J2").Lef t, _
Top:=Range("J2").Top, _
Width:=Range("J2:M12").Width, _
Height:=Range("J2:M12").Height).Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=Range("B3:C12"), _
PlotBy:=xlColumns
.HasLegend = False
End With


One thing to be aware of with the ChartObjects.Add method, is that when the
window's zoom setting is not at 100% , the placement and size of the
chartobject can be a little imprecise. Explicitly setting the chartobject's
Top, Left, Width & Height properties (as demonstrated in Sébastien's reply)
does not suffer from this problem.


Regards,
Vic Eldridge



"Landmine" wrote:

I would like to find a method to place a chart at a specific cell location
within a worksheet. I am adding a chart using data within the spreadsheet
and need to place the chart next to the data. I can't seem to find a method.

The code I am using to add the sheet is as follows and I would like to place
the top left hand corner in cell J2.

Thanks
BLandmine

sName = ActiveSheet.Name
Set sh = ActiveSheet
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=sh.Range("B3:C12"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:=sName
ActiveChart.HasLegend = False