Move TopLeft of Chart to a specified cell?

T

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Hello again.

I'm using a macro to chart a set of data. I'm also creating multiple charts.

How to I move the chart to a certain cell? Shape objects have a "TopLeftCell" property but it's read-only.

I would like to be able to move the top left corner of chart 1 to "$A$1", chart 2 to "$A$100", and so on.

Thanks,

-Tim
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Hi Tim,

Try something like the following:

Code: 
Sub foo()
Dim myShp As Shape
Application.ScreenUpdating = False
With Worksheets(1)
    Set myShp = .Shapes(.ChartObjects(1).Name)
    With .Range("d1")
        myShp.Top = .Top
        myShp.Left = .Left
        myShp.Height = .RowHeight * 30 '30 rows tall
    End With
End With
Application.ScreenUpdating = True
Set myShp = Nothing
End Sub
 
Upvote 0
Yes, TopLeftCell might be readonly but left and top are not. Unverified code follows:
Code: 
with activesheet
for i=1 to .chartobjects.count
    .chartobjects(i).left=.cells(i,1).left
    .chartobjects(i).top = .cells((i-1)*100+1,1).top
    next i
    end with
tmcfadden said:
Hello again.

I'm using a macro to chart a set of data. I'm also creating multiple charts.

How to I move the chart to a certain cell? Shape objects have a "TopLeftCell" property but it's read-only.

I would like to be able to move the top left corner of chart 1 to "$A$1", chart 2 to "$A$100", and so on.

Thanks,

-Tim
Click to expand...
 
Upvote 0
Thanks for the replies.

That worked once I figured out why I was still having a problem:
Code: 
Set chrt = bkRawData.Charts.Add
chrt.ChartType = xlXYScatterLines
chrt.SetSourceData Source:=shtSummary.Range( _
    strGraphRange), PlotBy:=xlColumns
chrt.Location Where:=xlLocationAsObject, Name:=shtSummary.Name
chrt.Shapes(1).Top = rng.Top
I didn't realize that I lost the "chrt" object once I changed the location from a sheet to a shape object ( chrt.Location ... ).

Therefore the last line would error out. It seems odd that I would lose the handle to the object. I'm sure there's a better way to keep it, but I just assume that the chart I just added will be at Shape index '.Shapes.Count'.

Works fine now ( using shtSummary.Shapes(shtSummary.Shapes.Count).Top = rngV_TopLeft.Top ), thanks.

-Tim
 
Upvote 0
Well, the object (a chartsheet) doesn't exist anymore! You can reestablish the reference with
set chrt=chrt.Location( Where:=xlLocationAsObject, Name:=shtSummary.Name)

and then use chrt.parent.top / left etc.

tmcfadden said:
{snip}
I didn't realize that I lost the "chrt" object once I changed the location from a sheet to a shape object ( chrt.Location ... ).

Therefore the last line would error out. It seems odd that I would lose the handle to the object. I'm sure there's a better way to keep it, but I just assume that the chart I just added will be at Shape index '.Shapes.Count'.
{snip}
Click to expand...
 
Upvote 0
tusharm said:
set chrt=chrt.Location( Where:=xlLocationAsObject, Name:=shtSummary.Name)
Click to expand...

I had thought about using that type of format but the documention on the .Location function didn't say anything about a return object. Thanks for the tip Tushar.

-Tim
 
Upvote 0
You are welcome.

tmcfadden said:
{snip}
I had thought about using that type of format but the documention on the .Location function didn't say anything about a return object. Thanks for the tip Tushar.

-Tim
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
See the Chart Height/ Width values in cells
Replies
1
Views
209
Frankroger
F
T
  • Solved
Chart point must move to point of click in chart
Replies
2
Views
441
tigger_na
T
J
  • Question
Excel chart with a line that will adjust certain cells
Replies
0
Views
239
jondavis1987
J
A
  • Solved
VBA Help!
Replies
2
Views
328
AusNewb
A
R
  • Solved
Replicating the Logarithmic trendline equation
Replies
1
Views
305
RockandGrohl
R

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top