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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>>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
Similar Threads
