Hi Ed
Thanks for the links and I am sure they work fine but the problem is that I
am trying to get this solution at work and my company won't let me download
any third party addins. I have some code that actually automatically adds
labels (see below) but my problem is that it appears the data has to be
sorted in ascending order by X value. Otherwise the labels don't match up
correctly.
Sub LittleChart_AttachLabelsToPoints()
' Dimension Variables.
Dim Counter As Integer, ChartName As Variant
Dim SourceWorksheet As Variant, xVals As Variant, xCell As Variant
Dim xLabel As Variant
Dim miniChart As ChartObject
On Error GoTo attachError
Set miniChart = ActiveSheet.ChartObjects(1)
' Store the name of the active chart in "ChartName".
ChartName = miniChart.Name
' Store the definition of the first series in "xVals".
xVals = miniChart.Chart.SeriesCollection(1).Formula
' These lines extract the name of the source worksheet from xVals.
SourceWorksheet = Left(xVals, InStr(1, xVals, "!") - 1)
SourceWorksheet = Right(SourceWorksheet, Len(SourceWorksheet) - InStr(1,
SourceWorksheet, "("))
If Left(SourceWorksheet, 1) = "," Then
SourceWorksheet = Right(SourceWorksheet, Len(SourceWorksheet) - 1)
End If
' Replace the actual source worksheet name with "xlsheet" so that the
' searches that follow will work correctly if the worksheet name
' contains commas.
xVals = Application.Substitute(xVals, SourceWorksheet, "xlSheet")
' More Processing of the xVals name.
xVals = Right(xVals, Len(xVals) - InStr(1, xVals, ","))
' If the chart is using "assumed" x-values then show an alert
' message.
If Left(xVals, 1) = "," Then
MsgBox "This X-Y scatter chart is using assumed x values. The macro
cannot contine."
' Exit the subroutine if "assumed" x-values are in use.
Exit Sub
End If
' More processing of the xVals name.
xVals = Left(xVals, InStr(1, xVals, ",") - 1)
' Put the original source worksheet name back into xVals, replacing
' "xlSheet".
xVals = Application.Substitute(xVals, "xlSheet", SourceWorksheet)
' Initialize a counter.
Counter = 1
' For each cell in the range xVals...
For Each xCell In Range(xVals)
'Get the value of the label next to the current x-value.
xLabel = xCell.Offset(0, -1).Value
If Len(xLabel) < 1 Then
Exit Sub
End If
'Attach a label to the current data point in the chart.
miniChart.Chart.SeriesCollection(1).Points(Counter).HasDataLabel = True
'Put the text("DataPoint1",for example) into the attached label.
miniChart.Chart.SeriesCollection(1).Points(Counter).DataLabel.Text =
xLabel
'Increment the counter.
Counter = Counter + 1
Next xCell 'loop until all done
Done:
'Clean variables
Set miniChart = Nothing
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 9
End With
Exit Sub
attachError:
MsgBox Err.Number & Err.Description, vbOKOnly
GoTo Done
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 8
End With
End Sub
