Data Labels for a chart in Excel

[JohnJack]

Hi,

I'm pretty good at using excel, and VBA macro, so I'm not a compelte
newbie. But I am having issues trying to change/link the data labels
to a 3rd series of points.

I have download an excel sheet (that has no macros) that contans a XY
scatter plot. It labels it's points with a 3rd series somehow but I
can't figure it out. I know you can label each point with the y or x
data, and I know there is a add-in package to do this, but my company
is against using 3rd party add ins. But why use an add-in package if
we don't need to? It just seems microsoft has hidden this in there
manuals etc.

Does anyone know how to do this (without an add-in?). Ie I have 3
columns of data. column 1 is the x data, column 2 is the y data, and
column 3 are to be the data labels for each point on the scatter plot.

Help?

Thanks in advance.

Jack

 

[Bernie Deitrick]

Jack,

Enter the desired labels in a range of cells (one for each data point - your third column), select
them, and then run a macro like the one below.


HTH,
Bernie
MS Excel MVP


Sub MacroLabels()

Dim i As Integer
Dim myL As Range

Set myL = Selection

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True
ActiveChart.SeriesCollection(1).DataLabels.Select
For i = 1 To ActiveChart.SeriesCollection(1).Points.Count
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Characters.Text = myL.Cells(i).Value
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Position = xlLabelPositionAbove
Next i
myL.Select
End Sub

 

[JohnJack]

Jack,

Enter the desired labels in a range of cells (one for each data point - your third column), select
them, and then run a macro like the one below.

HTH,
Bernie
MS Excel MVP

Sub MacroLabels()

Dim i As Integer
Dim myL As Range

Set myL = Selection

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True
ActiveChart.SeriesCollection(1).DataLabels.Select
For i = 1 To ActiveChart.SeriesCollection(1).Points.Count
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Characters.Text = myL.Cells(i).Value
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Position = xlLabelPositionAbove
Next i
myL.Select
End Sub

cheers..thanks