View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.charting
[email protected] nicholastoze@gmail.com is offline
external usenet poster
 
Posts: 2
Default How do I put custom data labels on charts in Excel?

Here's an alternative class that uses mouse move (some of the
improvements below can be used in class above too).
But you get some flicker if there are a lot of data points;
unfortunately it doesn't seem possible to show/hide datalabels, have
keep recreating them and each step causes a flicker.

Class
------PointMouseMoveLabeller

Option Explicit

Private WithEvents mChart As Excel.Chart

Private labels As Variant
Private prevArg1 As Long
Private prevArg2 As Long

Public Sub init(a_chart As Excel.Chart, some_labels As Variant)
Dim v As Variant
Set mChart = a_chart
labels = some_labels

mChart.ProtectGoalSeek = True
For Each v In mChart.SeriesCollection
v.HasDataLabels = False
Next
End Sub

Private Sub mChart_MouseMove(ByVal Button As Long, ByVal Shift As
Long, ByVal x As Long, ByVal y As Long)
Dim elementId As Long, arg1 As Long, arg2 As Long
mChart.GetChartElement x, y, elementId, arg1, arg2
If elementId = xlSeries Then
' Uses same label whatever the series, so don't care about
arg1 (Series#) changes.
If prevArg2 < arg2 Then ' And prevArg1 < arg1 Then
unlabelLastPoint
prevArg1 = arg1
prevArg2 = arg2
If arg2 <= UBound(labels) Then
With mChart.SeriesCollection(arg1).Points(arg2)
Application.ScreenUpdating = False
.HasDataLabel = True
.DataLabel.Font.Size = 8
.DataLabel.Text = labels(arg2)
.DataLabel.Border.Weight = xlHairline
.DataLabel.Shadow = True
.DataLabel.Interior.Color = 13434879
.DataLabel.Position = xlLabelPositionAbove
Application.ScreenUpdating = True
End With
End If
End If
Else
unlabelLastPoint
End If
End Sub

Private Sub unlabelLastPoint()
On Error GoTo sub_end
With mChart.SeriesCollection(prevArg1).Points(prevArg2)
If .HasDataLabel Then
.HasDataLabel = False
End If
End With
sub_end:
prevArg1 = 0
prevArg2 = 0
End Sub