+ Reply to Thread
Results 1 to 8 of 8

Activating a Chart object

  1. #1
    Hari Prasadh
    Guest

    Activating a Chart object

    Hi,

    Whats the correct syntax to access (activate) a chart within a chart sheet
    in an excel workbook?

    I tried

    Sheets("Chart1").Chartobjects("Chart 5").activate

    Also,

    Charts("Chart1").Chartobjects("Chart 5").activate

    But both of them gave run-time error.

    Basically I want to select a single chart within a chart sheet and change
    the Auto-Scale property to False.

    (I need to ultimately do this within all Excel Chart OLE's within a PPT, so
    my syntaxes actually have the name of the PPT session along with object name
    appended to them.)

    Regards,
    Hari
    India



  2. #2
    Andy Pope
    Guest

    Re: Activating a Chart object

    Hi,

    Have you tried using the macro recorder to get an idea of the syntax?

    I got this,
    Charts("Chart1").ChartObjects("Chart 1"). _
    Chart.ChartArea.AutoScaleFont = False

    Cheers
    Andy

    Hari Prasadh wrote:
    > Hi,
    >
    > Whats the correct syntax to access (activate) a chart within a chart sheet
    > in an excel workbook?
    >
    > I tried
    >
    > Sheets("Chart1").Chartobjects("Chart 5").activate
    >
    > Also,
    >
    > Charts("Chart1").Chartobjects("Chart 5").activate
    >
    > But both of them gave run-time error.
    >
    > Basically I want to select a single chart within a chart sheet and change
    > the Auto-Scale property to False.
    >
    > (I need to ultimately do this within all Excel Chart OLE's within a PPT, so
    > my syntaxes actually have the name of the PPT session along with object name
    > appended to them.)
    >
    > Regards,
    > Hari
    > India
    >
    >


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  3. #3
    Hari Prasadh
    Guest

    Re: Activating a Chart object

    Hi Andy,

    I tried recording macros and when I changed the constants like Chart1 and
    Chart 1 to variables I started getting errors.

    And I could check that the Names (Chart1 and Chart 1) were correct (after
    considerable difficulty) and also the variables which were holding these
    values were correct and hence couldnt understand what I was doing wrong.

    Also the problem could have been because Im not conversant with object level
    model for charts (im going through them presently but).

    Some queries: -

    a) Names of Charts are so difficult to find when im in a WORKSHEET. If I
    click on the chart border and see the name box it would display "Chart area"
    or if I click inside it would say plot area but not the name of the chart.
    Rather I would have to extend the black borders of the chart area and see
    the chart name displayed. Is there a better way than resorting to such
    distortion.

    b) Morever Im not able to see the names of the charts (mentioned in point a)
    above) when they are embedded in the CHARTSHETTS. I have to take the trouble
    of moving them to worksheets and stretching them and looking their names.
    The same stretching within chartsheets doesnt yield names when am within
    chartsheets. And yes, in case of Parent charts One cannnot do any
    stretching?

    c) Morever this is something I learned from my colleague just now. A Normal
    data worksheet can have many charts and None of them is tethered to one
    another. I thought that in the same way the charts within a Chartsheet are
    also not "linked" to each other. BUT, I see that if I have a chart sheet
    with a single chart in it then it is the "Parent chart" while any other
    charts that we add subsequently in that chart sheet have their chartarea
    within the chart area of the parent chart. Please throw some light on the
    same.

    d) When I tried moving the parent chart's location I got a message "To move
    a chart from its own sheet to an object, you must first remove all chart
    objects embedded in the sheet". On the other hand if I move the "child"
    chart from this same chart sheet to another worksheet/new chartsheet one can
    do flawlessly. So is the original chart in the chart sheet owner of that
    chartsheet?

    e) How do I refer to a parent chart in a chart sheet if i dont know its
    name?

    f) Suppose I have one embedded chart within a Chartsheet along with a parent
    sheet then do I use chartobject method to refer to the child chart. Recorded
    Macro tells me that it is so (please see the code
    "ParentAndChildInChartSheet" below) On the other hand Help File for
    ChartObject Object says that it "Represents an embedded chart on a
    worksheet."
    So why does recorded macro refer to the child with respect to chart objects
    while help file says that Chartobject object is only for Embedded
    WORKSHEETS?

    sub ParentAndChildInChartSheet()

    Sheets("Chart3").Select
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.AutoScaleFont = False
    ActiveWindow.Visible = False
    Windows("Book1").Activate
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.AutoScaleFont = False
    ActiveChart.Deselect

    End Sub

    In the above charts are there in Book1 workbook and while recording macro Im
    never switching workbooks so why does the macro use the
    "Windows("Book1").Activate"

    Also in this when there is only parent chart in a chart sheet and no more
    charts are there in the chart sheet, I get the following code: -

    Sub ParentOnlyInChartSheet()

    Sheets("Chart3").Select
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.AutoScaleFont = False

    End Sub

    So does that mean the parent chart is aleays referred to as active chart?

    Thanks a lot,
    Hari
    India

    "Andy Pope" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > Have you tried using the macro recorder to get an idea of the syntax?
    >
    > I got this,
    > Charts("Chart1").ChartObjects("Chart 1"). _
    > Chart.ChartArea.AutoScaleFont = False
    >
    > Cheers
    > Andy
    >
    > Hari Prasadh wrote:




  4. #4
    Andy Pope
    Guest

    Re: Activating a Chart object

    Hi,

    To see the name of a chartobject hold the shift key and then select the
    chart. The chartobjects name will then appear in the Name Box (next to
    the formula bar).

    You can embed multiple chart objects within a chart sheet but you can
    not embed chart objects within other chart objects. So when you try and
    change the location of a chart sheet with a chart object embeded in it
    you will get the warning message. This is because you are converting the
    chart sheet into a chart object.

    The following will set the axes for all charts and chartobjects.
    Sub Main()
    Dim chtTemp As Chart
    Dim shtTemp As Worksheet
    Dim objCht As ChartObject

    ' process all chart objects on each worksheet in workbook
    For Each shtTemp In ActiveWorkbook.Worksheets
    For Each chtobj In shtTemp.ChartObjects
    objCht.Chart.Axes(xlValue).TickLabels.AutoScaleFont = False
    Next
    Next

    ' process each chartsheet and chartobject in workbook
    For Each chtTemp In ActiveWorkbook.Charts
    chtTemp.Axes(xlValue).TickLabels.AutoScaleFont = False
    For Each objCht In chtTemp.ChartObjects
    objCht.Chart.Axes(xlValue).TickLabels.AutoScaleFont = False
    Next
    Next
    End Sub

    To get the parent of a chartobject on a worksheet you can use
    Activechart.parent.name

    To get the name of the chartobject on a chartsheet
    Activechart.parent.name

    To get the name of the chartsheet of a chartobject on a chartsheet
    Activechart.parent.parent.name

    The appearence of code like Windows("Book1").Activate
    is because the marco recorder is not really intelligent it just
    generates code for all your actions. It's a pretty good place to start
    but you do need to remove redundent code.

    Hopefully that covers all the questions.

    Cheers
    Andy

    Hari Prasadh wrote:
    > Hi Andy,
    >
    > I tried recording macros and when I changed the constants like Chart1 and
    > Chart 1 to variables I started getting errors.
    >
    > And I could check that the Names (Chart1 and Chart 1) were correct (after
    > considerable difficulty) and also the variables which were holding these
    > values were correct and hence couldnt understand what I was doing wrong.
    >
    > Also the problem could have been because Im not conversant with object level
    > model for charts (im going through them presently but).
    >
    > Some queries: -
    >
    > a) Names of Charts are so difficult to find when im in a WORKSHEET. If I
    > click on the chart border and see the name box it would display "Chart area"
    > or if I click inside it would say plot area but not the name of the chart.
    > Rather I would have to extend the black borders of the chart area and see
    > the chart name displayed. Is there a better way than resorting to such
    > distortion.
    >
    > b) Morever Im not able to see the names of the charts (mentioned in point a)
    > above) when they are embedded in the CHARTSHETTS. I have to take the trouble
    > of moving them to worksheets and stretching them and looking their names.
    > The same stretching within chartsheets doesnt yield names when am within
    > chartsheets. And yes, in case of Parent charts One cannnot do any
    > stretching?
    >
    > c) Morever this is something I learned from my colleague just now. A Normal
    > data worksheet can have many charts and None of them is tethered to one
    > another. I thought that in the same way the charts within a Chartsheet are
    > also not "linked" to each other. BUT, I see that if I have a chart sheet
    > with a single chart in it then it is the "Parent chart" while any other
    > charts that we add subsequently in that chart sheet have their chartarea
    > within the chart area of the parent chart. Please throw some light on the
    > same.
    >
    > d) When I tried moving the parent chart's location I got a message "To move
    > a chart from its own sheet to an object, you must first remove all chart
    > objects embedded in the sheet". On the other hand if I move the "child"
    > chart from this same chart sheet to another worksheet/new chartsheet one can
    > do flawlessly. So is the original chart in the chart sheet owner of that
    > chartsheet?
    >
    > e) How do I refer to a parent chart in a chart sheet if i dont know its
    > name?
    >
    > f) Suppose I have one embedded chart within a Chartsheet along with a parent
    > sheet then do I use chartobject method to refer to the child chart. Recorded
    > Macro tells me that it is so (please see the code
    > "ParentAndChildInChartSheet" below) On the other hand Help File for
    > ChartObject Object says that it "Represents an embedded chart on a
    > worksheet."
    > So why does recorded macro refer to the child with respect to chart objects
    > while help file says that Chartobject object is only for Embedded
    > WORKSHEETS?
    >
    > sub ParentAndChildInChartSheet()
    >
    > Sheets("Chart3").Select
    > ActiveSheet.ChartObjects("Chart 2").Activate
    > ActiveChart.Axes(xlValue).Select
    > Selection.TickLabels.AutoScaleFont = False
    > ActiveWindow.Visible = False
    > Windows("Book1").Activate
    > ActiveChart.Axes(xlValue).Select
    > Selection.TickLabels.AutoScaleFont = False
    > ActiveChart.Deselect
    >
    > End Sub
    >
    > In the above charts are there in Book1 workbook and while recording macro Im
    > never switching workbooks so why does the macro use the
    > "Windows("Book1").Activate"
    >
    > Also in this when there is only parent chart in a chart sheet and no more
    > charts are there in the chart sheet, I get the following code: -
    >
    > Sub ParentOnlyInChartSheet()
    >
    > Sheets("Chart3").Select
    > ActiveChart.Axes(xlValue).Select
    > Selection.TickLabels.AutoScaleFont = False
    >
    > End Sub
    >
    > So does that mean the parent chart is aleays referred to as active chart?
    >
    > Thanks a lot,
    > Hari
    > India
    >
    > "Andy Pope" <[email protected]> wrote in message
    > news:[email protected]...
    >
    >>Hi,
    >>
    >>Have you tried using the macro recorder to get an idea of the syntax?
    >>
    >>I got this,
    >>Charts("Chart1").ChartObjects("Chart 1"). _
    >>Chart.ChartArea.AutoScaleFont = False
    >>
    >>Cheers
    >>Andy
    >>
    >>Hari Prasadh wrote:

    >
    >
    >


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  5. #5
    Hari Prasadh
    Guest

    Re: Activating a Chart object

    Hi,

    Thanks for your detailed explanation

    Seeing your along and comparing with macro recorder am able to make sense of
    the kind of syntax needed to do similar things.

    Im now able to understand that even chartsheets work on the basis of the
    chart objects syntax. (wonder why MS has written in the help about
    chartobjects saying "Represents an embedded chart on a
    worksheet." when even chartsheets use the chartobject concept.)

    Grateful for your Shift + Click for chart name tip.

    Thanks a lot,
    Hari
    India

    "Andy Pope" <[email protected]> wrote in message
    news:[email protected]...



  6. #6
    Jon Peltier
    Guest

    Re: Activating a Chart object

    Hari -

    The chart object represents a chart embedded in any sheet, whether it's
    a chart sheet or worksheet. But don't get confused and try to access the
    chart object container of a chart which is the chart sheet itself, not
    embedded in the sheet.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    Hari Prasadh wrote:
    > Hi,
    >
    > Thanks for your detailed explanation
    >
    > Seeing your along and comparing with macro recorder am able to make sense of
    > the kind of syntax needed to do similar things.
    >
    > Im now able to understand that even chartsheets work on the basis of the
    > chart objects syntax. (wonder why MS has written in the help about
    > chartobjects saying "Represents an embedded chart on a
    > worksheet." when even chartsheets use the chartobject concept.)
    >
    > Grateful for your Shift + Click for chart name tip.
    >
    > Thanks a lot,
    > Hari
    > India
    >
    > "Andy Pope" <[email protected]> wrote in message
    > news:[email protected]...
    >
    >


  7. #7
    Hari Prasadh
    Guest

    Re: Activating a Chart object

    Hi Jon,

    Thanks for the clarification.

    I am sure I have come across an article/web-page which says that if one
    wants to control charts through programming then its better to do that with
    embedded charts in Worksheets rather than the charts/embedded charts within
    a ChartSheet. Reason cited was that syntax is more consistent/clearer/easier
    (or something similar).

    If you have the link please give the same to me. (I think it was probably
    your article.)

    Thanks a lot,
    Hari
    India

    "Jon Peltier" <[email protected]> wrote in message
    news:O%[email protected]...
    > Hari -
    >
    > The chart object represents a chart embedded in any sheet, whether it's a
    > chart sheet or worksheet. But don't get confused and try to access the
    > chart object container of a chart which is the chart sheet itself, not
    > embedded in the sheet.
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    >
    > Hari Prasadh wrote:
    >> Hi,
    >>
    >> Thanks for your detailed explanation
    >>
    >> Seeing your along and comparing with macro recorder am able to make sense
    >> of the kind of syntax needed to do similar things.
    >>
    >> Im now able to understand that even chartsheets work on the basis of the
    >> chart objects syntax. (wonder why MS has written in the help about
    >> chartobjects saying "Represents an embedded chart on a
    >> worksheet." when even chartsheets use the chartobject concept.)
    >>
    >> Grateful for your Shift + Click for chart name tip.
    >>
    >> Thanks a lot,
    >> Hari
    >> India
    >>
    >> "Andy Pope" <[email protected]> wrote in message
    >> news:[email protected]...




  8. #8
    Registered User
    Join Date
    05-10-2020
    Location
    Sweden
    MS-Off Ver
    Owuro
    Posts
    1

    My answer

    Awesome thanks
    Don’t keep a dog and bark yourself

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1