Found what I needed (mostly):
Code:
Sub AttachLabelsToPoints()
'Dimension variables.
Dim PointCount As Integer, xVals() As String
Dim SeriesCount As Integer
Dim Max As Integer
Max = ActiveChart.SeriesCollection.Count
ReDim xVals(1 To Max)
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False
'Store the formula for each series in "xVals(SeriesCount)".
For SeriesCount = 1 To Max
On Error Resume Next
xVals(SeriesCount) = ActiveChart.SeriesCollection(SeriesCount).Formula
Next SeriesCount
'Extract the range for the data from xVals.
For SeriesCount = 1 To Max
On Error Resume Next
xVals(SeriesCount) = Mid(xVals(SeriesCount), InStr(InStr(xVals(SeriesCount), ","), xVals(SeriesCount), Mid(Left(xVals(SeriesCount), InStr(xVals(SeriesCount), "!") - 1), 9)))
xVals(SeriesCount) = Left(xVals(SeriesCount), InStr(InStr(xVals(SeriesCount), "!"), xVals(SeriesCount), ",") - 1)
Do While Left(xVals(SeriesCount), 1) = ","
xVals(SeriesCount) = Mid(xVals(SeriesCount), 2)
Loop
Next SeriesCount
'Attach a label to each data point in the chart.
For SeriesCount = 1 To Max
For PointCount = 1 To Range(xVals(SeriesCount)).Cells.Count
With ActiveChart.SeriesCollection(SeriesCount).Points(PointCount)
.HasDataLabel = True
.DataLabel.Border.ColorIndex = 1
Select Case SeriesCount
Case Is = 1
.DataLabel.Interior.ColorIndex = 4
Case Is = 2
.DataLabel.Interior.ColorIndex = 3
End Select
.DataLabel.Text = "=" & Range(xVals(SeriesCount)).Cells(PointCount, 1).Offset(0, -1).Address(True, True, xlR1C1, True)
End With
Next PointCount
Next SeriesCount
End Sub
My data is organized in four columns: Labels, X values, Y values for met deadlines, Y values for missed deadlines.
My X values come from the project completion dates on another spreadsheet.
My 'met' and 'missed' columns are conditionally formatted to display either the desired height of the data point or "N/A#", based on whether or not the completion dates fall after the pre-determined due dates.
If I have 7 'met' deadlines and 3 'missed' deadlines, series 1 will have 7 data points with green labels while series 2 has 3 data points with red labels and the text values of the labels are cell references to the first data column.
My only issue is that because only the red or the green data point is created for any given deadline, if the status changes, the data point that suddenly becomes visible is not one that received the desired labeling/formatting. What I'd really like is to have all points created for both series, then conditionally determine which to show and which to hide after their appearance has been set.
Edit : Sonofabitch
This failed functionality, with only those data points which will be displayed actually being formatted, apparently does not apply to all versions of Excel. It most certainly does apply to Excel2003, which is what I've got at work; but when I fiddled with it in Excel2007, which is what Taamar has on her computer, ALL of my data points received the proper formatting and the whole thing worked beautifully. Moot point because I'm pretty sure there's no way I'm going to talk TIAA-CREF into upgrading their license, but I am now torn between the slick functionality combined with the hideous "Ribbon" menu interface of 2007 vs the deficient functionality combined with the simple utilitarian menu interface of 2003.
Login
Register
FAQ
Search
