Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003- Tabbing to a specific location in a cell | Excel Discussion (Misc queries) | |||
cell location containing specific text | Excel Worksheet Functions | |||
how do I draw a line in a chart at a specific location | Charts and Charting in Excel | |||
hyperlink an excel cell to a specific location wthin application f | Excel Discussion (Misc queries) | |||
ListBox Result to a Specific Cell Location | Excel Programming |