ChloeRadshaw
06-10-2009, 09:53 AM
So this piece of code should remove any empty labels in a pie chart - However notice that if the data label is removed and the report rerun then the data label is not put back.
Adding .Points(iPts).HasDataLabel = False results in the formatiting of the labels being lost.
This is a complete PAIN. Does anyone have any ideas on how to add / remove labels on pie charts please.
Option Explicit
Sub remove0Labels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim dataLabelType As Integer
Dim po As Point
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, _
"No Chart Selected"
Else
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
'dataLabelType = .DataLaboels.Type.xlValue
nPts = .Points.Count
aVals = .values
For iPts = 1 To nPts
Set po = .Points(iPts)
If aVals(iPts) = 0 Then
'po.DataLabel.NumberFormat
.Points(iPts).HasDataLabel = False
Else
End If
Next
End If
End With
Next
End If
End Sub
Adding .Points(iPts).HasDataLabel = False results in the formatiting of the labels being lost.
This is a complete PAIN. Does anyone have any ideas on how to add / remove labels on pie charts please.
Option Explicit
Sub remove0Labels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim dataLabelType As Integer
Dim po As Point
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, _
"No Chart Selected"
Else
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
'dataLabelType = .DataLaboels.Type.xlValue
nPts = .Points.Count
aVals = .values
For iPts = 1 To nPts
Set po = .Points(iPts)
If aVals(iPts) = 0 Then
'po.DataLabel.NumberFormat
.Points(iPts).HasDataLabel = False
Else
End If
Next
End If
End With
Next
End If
End Sub