Have any Excel geniuses written a macro that looks at a
pie chart and spreads the labels to ensure that they
don't overlap.

We have an application that generates thousands of pie
charts - we go through them and if labels are overlapping
then we fix them by dragging manually. Is there even a
partial solution? I'm not hopeful, but then I'm always
surprised by what you guys come up with.

Daniel

Any chance of being rewarded if we can surprise you?
if so how much

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Daniel Bonallack" wrote:

Have any Excel geniuses written a macro that looks at a
pie chart and spreads the labels to ensure that they
don't overlap.

We have an application that generates thousands of pie
charts - we go through them and if labels are overlapping
then we fix them by dragging manually. Is there even a
partial solution? I'm not hopeful, but then I'm always
surprised by what you guys come up with.

Daniel

I'm definitely all in favor of paying for expertise -
it's only fair. But big firms carry one big
disadvantage - it's certain that if I asked the bank to
pay for it, they would send me to our department of in-
house programmers rather than someone on this forum.

Thanks for responding, but for the moment I'll struggle
through.

Daniel



-----Original Message-----
Any chance of being rewarded if we can surprise you?
if so how much

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Daniel Bonallack" wrote:

Have any Excel geniuses written a macro that looks at
a
pie chart and spreads the labels to ensure that they
don't overlap.

We have an application that generates thousands of pie
charts - we go through them and if labels are
overlapping
then we fix them by dragging manually. Is there even
a
partial solution? I'm not hopeful, but then I'm
always
surprised by what you guys come up with.

Daniel


.

Hi Daniel,

Perhaps you could change the position of each label in
turn:

Sub test()
Dim pos1 As Boolean, pos2 As Boolean, pos3 As Boolean
Dim ChObj As Chart

Set ChObj = ActiveChart ' or loop charts
pos1 = False: pos2 = False: pos3 = True
With ChObj.SeriesCollection(1).DataLabels
pos1 = False: pos2 = False: pos3 = True
With chobj.SeriesCollection(1).DataLabels

For i = 1 To .Count
With .Item(i)
If pos3 Then
.Position = xlLabelPositionOutsideEnd
pos1 = True
pos3 = False
ElseIf pos1 Then
.Position = xlLabelPositionInsideEnd
pos2 = True
pos1 = False
Else
.Position = xlLabelPositionCenter
pos2 = False
pos3 = True
End If
End With
Next
End With

End Sub


If something along the lines of the above has
possibilities, you may also want to change the colour of
the font, if it's "inside" the pie. Record a macro and
slot in. Also loop all charts in sheet / wb.

Regards,
Peter

-----Original Message-----
Have any Excel geniuses written a macro that looks at a
pie chart and spreads the labels to ensure that they
don't overlap.

We have an application that generates thousands of pie
charts - we go through them and if labels are overlapping
then we fix them by dragging manually. Is there even a
partial solution? I'm not hopeful, but then I'm always
surprised by what you guys come up with.

Daniel
.

Something went amiss with my copy/paste. Obviously
following lines should only appear once:

pos1 = False: pos2 = False: pos3 = True
With chobj.SeriesCollection(1).DataLabels

Peter

-----Original Message-----
Hi Daniel,

Perhaps you could change the position of each label in
turn:

Sub test()
Dim pos1 As Boolean, pos2 As Boolean, pos3 As Boolean
Dim ChObj As Chart

Set ChObj = ActiveChart ' or loop charts
pos1 = False: pos2 = False: pos3 = True
With ChObj.SeriesCollection(1).DataLabels
pos1 = False: pos2 = False: pos3 = True
With chobj.SeriesCollection(1).DataLabels

For i = 1 To .Count
With .Item(i)
If pos3 Then
.Position = xlLabelPositionOutsideEnd
pos1 = True
pos3 = False
ElseIf pos1 Then
.Position = xlLabelPositionInsideEnd
pos2 = True
pos1 = False
Else
.Position = xlLabelPositionCenter
pos2 = False
pos3 = True
End If
End With
Next
End With

End Sub


If something along the lines of the above has
possibilities, you may also want to change the colour of
the font, if it's "inside" the pie. Record a macro and
slot in. Also loop all charts in sheet / wb.

Regards,
Peter

-----Original Message-----
Have any Excel geniuses written a macro that looks at a
pie chart and spreads the labels to ensure that they
don't overlap.

We have an application that generates thousands of pie
charts - we go through them and if labels are
overlapping
then we fix them by dragging manually. Is there even a
partial solution? I'm not hopeful, but then I'm always
surprised by what you guys come up with.

Daniel
.

.

Thanks very much Peter - I'll try out your code
-----Original Message-----
Something went amiss with my copy/paste. Obviously
following lines should only appear once:

pos1 = False: pos2 = False: pos3 = True
With chobj.SeriesCollection(1).DataLabels

Peter

-----Original Message-----
Hi Daniel,

Perhaps you could change the position of each label in
turn:

Sub test()
Dim pos1 As Boolean, pos2 As Boolean, pos3 As Boolean
Dim ChObj As Chart

Set ChObj = ActiveChart ' or loop charts
pos1 = False: pos2 = False: pos3 = True
With ChObj.SeriesCollection(1).DataLabels
pos1 = False: pos2 = False: pos3 = True
With chobj.SeriesCollection(1).DataLabels

For i = 1 To .Count
With .Item(i)
If pos3 Then
.Position = xlLabelPositionOutsideEnd
pos1 = True
pos3 = False
ElseIf pos1 Then
.Position = xlLabelPositionInsideEnd
pos2 = True
pos1 = False
Else
.Position = xlLabelPositionCenter
pos2 = False
pos3 = True
End If
End With
Next
End With

End Sub


If something along the lines of the above has
possibilities, you may also want to change the colour
of
the font, if it's "inside" the pie. Record a macro and
slot in. Also loop all charts in sheet / wb.

Regards,
Peter

-----Original Message-----
Have any Excel geniuses written a macro that looks at
a
pie chart and spreads the labels to ensure that they
don't overlap.

We have an application that generates thousands of pie
charts - we go through them and if labels are
overlapping
then we fix them by dragging manually. Is there even
a
partial solution? I'm not hopeful, but then I'm
always
surprised by what you guys come up with.

Daniel
.

.

.

Daniel

have you ever used a pie of type 6 in the chartwizard?
ActiveChart.ChartType = xlBarOfPie

imho this layout gives a good 'look and feel' and solves most overlap
problems

.SplitType = xlSplitByPercentValue
.SplitValue = 5
.SecondPlotSize = 50

In case you'd get too many grouped items you'd need to 'post process' only
the labels of the 'barred' smaller items, which is relatively easy as it's
just vertical spreading...



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Daniel Bonallack" wrote:

Have any Excel geniuses written a macro that looks at a
pie chart and spreads the labels to ensure that they
don't overlap.

We have an application that generates thousands of pie
charts - we go through them and if labels are overlapping
then we fix them by dragging manually. Is there even a
partial solution? I'm not hopeful, but then I'm always
surprised by what you guys come up with.

Daniel