Hi,

I'm currently struggling to change the Title and the Legend of pie
charts that are generated by passing data from Word to Excel, then
copying the chart over to Word. (I'm using Word + Excel 2000).

The VBA is use works OK, and generates a reasonable looking chart.

However, no matter what I try, I cannot alter the chart title, or the
legend. Let me expand on this a little.

1. The Chart Title is always centered. However, the legend (if there a
lot of entries in the chart) spills over the title and obscures it.
Therefore, I need to position the title on the far left of the chart.

2. If I have more than, say, eight elements in the chart, the legend
spills beyond the bottom of the chart, but there seems to be huge
spacing gaps between each entry on the legend.
However, if I create a chart using an Excel Range and the Chart Wizard,
it seems to easily accomodate ten or more entries, and space them nice
and evenly.

It does not seem to matter whether I set "Legend.AutoScaleFont" to
true or false, or whether I try and position the title. It always shows
up the exactly the same.


I thought I had cracked this, but I obviously need to do some fine
tweaking. Any suggestions appreciated.




Code below:

//// cNumRows and cNumCols are variables used to populate an Excel
Range from a Word VBA array of data

Set oChart = oSheet.ChartObjects.Add.Chart
oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows,
cNumCols), PlotBy:= _
xlColumns
oChart.ChartType = xl3DPieExploded
oChart.RightAngleAxes = True
oChart.PlotArea.Height = 215
oChart.PlotArea.Width = 215
oChart.PlotArea.left = 5
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Border.LineStyle = -4142
oChart.Elevation = 30
oChart.Rotation = 80
oChart.Pie3DGroup.VaryByCategories = True
oChart.HasTitle = True
oChart.ChartTitle.Top = 0
oChart.ChartTitle.left = 0
oChart.ChartTitle.Characters.Text = "Current Asset
Allocation"
oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent,
LegendKey:=False _
, HasLeaderLines:=True
oChart.SeriesCollection(1).DataLabels.Font.Size = 8

oChart.HasLegend = True
With oChart.Legend
.Legend.Shadow = True
'.Legend.Position = xlLegendPositionRight
.Legend.AutoScaleFont = False
.Legend.Font.Size = 3
End With



Thanks
Neil.