Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete any Lables with "0%" value in pie chart
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete any Lables with "0%" value in pie chart
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete any Lables with "0%" value in pie chart
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why do i "sometimes" need to "edit chart object" before "source da | Charts and Charting in Excel | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
delete rows if cell in row contains "a" or "o" or empty | Excel Programming | |||
Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc | Excel Programming |