VBA - Accessing the Active Chart's SHAPE

[John McGraw]

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 ]

[Tom Schreiner]

Is this correct?
ActiveChart.Left = Sheet1.Cells(3, 2).Left
Tom

[Ivan F Moala]

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

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

[John McGraw]

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?

[John McGraw]

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?