Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2006

Tip: Looking for answers? Try searching our database.

Place chart at specific cell location

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Landmine - 28 Mar 2006 14:29 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.