Formatting pie charts via VBA

[neil]

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.

 

[Andy Pope]

Hi,

Your chart title positioning code works for me in a test environment.
The legend part I had to tweak slightly.

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

Does your code raise an error (assuming no error suppression is on) or
does it just not work?

Cheers
Andy

 

[neil]

Does your code raise an error (assuming no error suppression is on) or
does it just not work?

It simply does not work. No error messages are displayed.

Again, when I tried it with your tweaked code, it made no difference.
In fact if I set the font size to 30 instead of 3, it makes no
difference. The legend remains exactly the same.

Having looked through the documentation, most of the code seems
correct, but Excel is not co-operating.

The version of Word/Excel I am using is 9.02720 if this makes any
difference.

Thanks
Neil.

 

[Andy Pope]

Hi,

If you want you can send me, offline, your file and I will take a look.

Cheers
Andy

Does your code raise an error (assuming no error suppression is on) or
does it just not work?

It simply does not work. No error messages are displayed.

Again, when I tried it with your tweaked code, it made no difference.
In fact if I set the font size to 30 instead of 3, it makes no
difference. The legend remains exactly the same.

Having looked through the documentation, most of the code seems
correct, but Excel is not co-operating.

The version of Word/Excel I am using is 9.02720 if this makes any
difference.

Thanks
Neil.