Results 1 to 5 of 5

VBA - Accessing the Active Chart's SHAPE

This is a discussion on VBA - Accessing the Active Chart's SHAPE within the Excel Questions forums, part of the Question Forums category; I am automating chart creation and I am trying to see how I can access the chart as its shape ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    76

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

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

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    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





    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    76

    Default

    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?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    76

    Default

    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?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com