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 Where:=xlLocationAsObject, Name:=sName
ActiveChart.HasLegend = False
sebastienm - 28 Mar 2006 18:31 GMT
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

Signature
Regards,
Sébastien
<http://www.ondemandanalysis.com>
> 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
[quoted text clipped - 14 lines]
> ActiveChart.Location Where:=xlLocationAsObject, Name:=sName
> ActiveChart.HasLegend = False
Vic Eldridge - 29 Mar 2006 05:34 GMT
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").Left, _
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
> 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
[quoted text clipped - 14 lines]
> ActiveChart.Location Where:=xlLocationAsObject, Name:=sName
> ActiveChart.HasLegend = False