VBA - Accessing the Active Chart's SHAPE

John McGraw

Board Regular
Joined
Feb 25, 2002
Messages
76
I am automating chart creation and I am trying to see how I can access the chart as its shape so I can change the size. But unless I hardcode a specific name in the shape() index, I cannot get it to work.

I am now trying the following: (The chart I am working with is active)

ActiveSheet.Shapes(ActiveChart.Name).Left = Sheet1.Cells(3, 2).Left


This ALMOST works, but the ActiveChart.Name property also includes the name of the sheet which is causing a "name not found" error when the above code is run.

I have tried watching the macro-creator but it always hardcodes the name of the chart when accessing the shapes object. (I wonder how IT gets the name! :) )

What can I do to access the shape of the current chart without hard-coding the actual name into my code?

Thanks very much for any help. This problem is driving me nuts!

John
This message was edited by John McGraw on 2002-04-16 00:19
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
What you should do is name your chart
from the code then you can reference it latter....or just assign a variable to it.

eg
<pre/>
Sub MakeChart()

Dim ch As ChartObject

Set ch = Worksheets("sheet1").ChartObjects.Add(100, 30, 400, 250)

With ch
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("B11:B16"), PlotBy:=xlColumns
.Chart.HasTitle = False
.Chart.Axes(xlCategory, xlPrimary).HasTitle = False
.Chart.Axes(xlValue, xlPrimary).HasTitle = False
.Name = "MyChart1"
End With

End Sub

</pre>
 
Upvote 0
On 2002-04-16 02:58, Ivan F Moala wrote:
What you should do is name your chart
from the code then you can reference it latter....or just assign a variable to it.

I tried this over and over!!! I tried
activechart.name = "MyName"

But the name never changed. In your example it seems to work. Is it because you actually created a chart object? You cant change the name of a chart unless you created it as a chart object?

I tried the folowing and it didnt work:
(ChartRange is the range of the chart)
-----------------------
ChartRange.Select
Charts.add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Name = "MyChart"
ActiveSheet.Shapes("MyChart").Left = Sheet1.Cells(3, 2).Left
------------------------

If I run this I get the error:
"The item with the specified name wasnt found"

I guess I answered my own question, it is necessary to "dim" a chart object. Am I correct?
 
Upvote 0
On 2002-04-16 00:34, TsTom wrote:
Is this correct?
ActiveChart.Left = Sheet1.Cells(3, 2).Left
Tom

No its not because the activechart object (or any chart object for the matter) does not have a left, top, height or width properties.

I'm not 100% clear on how charts code works, but they seem to be a combination of 2 different objects. (_Without_ one or the other being a parent) I think the chart object and a shape object for the chart both represent the chart but in different ways, and strangly enough (this is the part I dont understand) they are not related.

Would anyone be able to clarify this?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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