PDA

View Full Version : Interesting Issue With Labeling Data Points



itipu
04-02-2010, 01:59 AM
I have a simple graph with one or more series... and I am trying to label each data poing... the following code works fine...

Dim aRng As Range
Dim srsNew As Series
Dim YValues As String
Dim XValues As String
Set aRng = Worksheets("Data").Range("C2:C30")

YValues = "=Data!R2C5:R30C5"
XValues = "=Data!R2C4:R30C4"

Set srsNew = .SeriesCollection.NewSeries
With srsNew
.Name = rngDataSource.Cells(1, iSrsIx + 1)
.Values = YValues
.XValues = XValues
.MarkerBackgroundColorIndex = xlNone
.MarkerSize = 5
.ApplyDataLabels
With .Points
For i = 1 To .Count
With .Item(i)
.DataLabel.Text = "='" & aRng.Parent.Name & "'!" _
& aRng.Cells(i).Address(ReferenceStyle:=xlR1C1)
End With
Next i
End With
End With

However this only works well for a 1 Series Graph (i.e. see sheet Data in attached excel workbook), if you have 2 Series or more graph and have empty Y values (and so for this specific XY there is no data point) this code fails.. See sheet Data_2 or more Series..

I am wondering how to work around that but seems rather impossible from where I am..

P.S I can provide the whole routine as obviously I am seeting ranges and X&Y values dynamically.. but code above is just an example.

p45cal
04-02-2010, 11:49 AM
Your code works fine in xl2007.
Moving to xl2003, it didn't, failing on trying to put text into a data label which wasn't.

I got round it, inelegantly, with this snippet:
With .Points
For i = 1 To .Count
With .Item(i)
ItHasADataLabel = False
On Error Resume Next
ItHasADataLabel = .HasDataLabel
On Error GoTo 0
If ItHasADataLabel Then .DataLabel.Text = "='" & aRng.Parent.Name & "'!" _
& aRng.Cells(i).Address(ReferenceStyle:=xlR1C1)
End With
Next i
End With