Hi,
I have a macro that automatically applies formatted data labels to a pie
chart.
Data labels include Value, % of the Pie and the Legend Color.

The Chart Sheet is not a pivot chart but the data series points to a range
that contains data from a pivot table. I do it this way because Pivot Charts
seem to have limited formating and other options.

Because of how I point to the data range, it doesn't update the range if the
pivot table cells get smaller when applying filters.

This causes some "Zero" values in the data series if the pivot table gets
smaller.
This works fine for me in the pie chart since the value is Zero.

My question is:
How do I search through all the data points in the pie chart series, find
any datapoints where the data value = 0 and remove the data label from that
data point. Something like For Each Datapoint in ActiveChart..... is what
I"m thinking, I just can't figure out the syntax.

Here's the recorded macro where I just clicked on the various labels and
deleted them.

Sub Macro4()
Dim thisChart As Chart

Set thisChart = ActiveChart

thisChart.SeriesCollection(1).DataLabels.Select
thisChart.SeriesCollection(1).Points(12).DataLabel.Select
Selection.Delete
thisChart.SeriesCollection(1).DataLabels.Select
thisChart.SeriesCollection(1).Points(14).DataLabel.Select
Selection.Delete
thisChart.SeriesCollection(1).DataLabels.Select
thisChart.SeriesCollection(1).Points(13).DataLabel.Select
Selection.Delete
End Sub

Thanks,
MikeZz

From a quick Google search action:

http://www.ozgrid.com/forum/showthread.php?t=89364


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"MikeZz" wrote:

> Hi,
> I have a macro that automatically applies formatted data labels to a pie
> chart.
> Data labels include Value, % of the Pie and the Legend Color.
>
> The Chart Sheet is not a pivot chart but the data series points to a range
> that contains data from a pivot table. I do it this way because Pivot Charts
> seem to have limited formating and other options.
>
> Because of how I point to the data range, it doesn't update the range if the
> pivot table cells get smaller when applying filters.
>
> This causes some "Zero" values in the data series if the pivot table gets
> smaller.
> This works fine for me in the pie chart since the value is Zero.
>
> My question is:
> How do I search through all the data points in the pie chart series, find
> any datapoints where the data value = 0 and remove the data label from that
> data point. Something like For Each Datapoint in ActiveChart..... is what
> I"m thinking, I just can't figure out the syntax.
>
> Here's the recorded macro where I just clicked on the various labels and
> deleted them.
>
> Sub Macro4()
> Dim thisChart As Chart
>
> Set thisChart = ActiveChart
>
> thisChart.SeriesCollection(1).DataLabels.Select
> thisChart.SeriesCollection(1).Points(12).DataLabel.Select
> Selection.Delete
> thisChart.SeriesCollection(1).DataLabels.Select
> thisChart.SeriesCollection(1).Points(14).DataLabel.Select
> Selection.Delete
> thisChart.SeriesCollection(1).DataLabels.Select
> thisChart.SeriesCollection(1).Points(13).DataLabel.Select
> Selection.Delete
> End Sub
>
> Thanks,
> MikeZz
>

Thanks Wigi, you made it look so easy if I just put in the right words to
search!

An addition to this one is how do I check each Data Label and if it = a
certain value, make that pie slice a certain color? I want to have this so
that I can "syncronize" the colors of various changing charts so that the all
match.

Make the "Car" slice = Green
"House" slice = Red
"Utilities" slice = Blue
(regardless of the order in which they appear in the data series)


For instance if my pie data looks like this
Label Data
Car 1000
House 1500
Utilities 300


"Wigi" wrote:

> From a quick Google search action:
>
> http://www.ozgrid.com/forum/showthread.php?t=89364
>
>
> --
> Wigi
> http://www.wimgielis.be = Excel/VBA, soccer and music
>
>
> "MikeZz" wrote:
>
> > Hi,
> > I have a macro that automatically applies formatted data labels to a pie
> > chart.
> > Data labels include Value, % of the Pie and the Legend Color.
> >
> > The Chart Sheet is not a pivot chart but the data series points to a range
> > that contains data from a pivot table. I do it this way because Pivot Charts
> > seem to have limited formating and other options.
> >
> > Because of how I point to the data range, it doesn't update the range if the
> > pivot table cells get smaller when applying filters.
> >
> > This causes some "Zero" values in the data series if the pivot table gets
> > smaller.
> > This works fine for me in the pie chart since the value is Zero.
> >
> > My question is:
> > How do I search through all the data points in the pie chart series, find
> > any datapoints where the data value = 0 and remove the data label from that
> > data point. Something like For Each Datapoint in ActiveChart..... is what
> > I"m thinking, I just can't figure out the syntax.
> >
> > Here's the recorded macro where I just clicked on the various labels and
> > deleted them.
> >
> > Sub Macro4()
> > Dim thisChart As Chart
> >
> > Set thisChart = ActiveChart
> >
> > thisChart.SeriesCollection(1).DataLabels.Select
> > thisChart.SeriesCollection(1).Points(12).DataLabel.Select
> > Selection.Delete
> > thisChart.SeriesCollection(1).DataLabels.Select
> > thisChart.SeriesCollection(1).Points(14).DataLabel.Select
> > Selection.Delete
> > thisChart.SeriesCollection(1).DataLabels.Select
> > thisChart.SeriesCollection(1).Points(13).DataLabel.Select
> > Selection.Delete
> > End Sub
> >
> > Thanks,
> > MikeZz
> >