Attaching labels to x-y data points on graphs
Attaching labels to x-y data points on graphs
(OP)
Ok, this could be really straight forward, but I cant figure it!
I have a set of x-y data points (superficial gas and liquid velocities if you must know). Now I want to plot these on an x-y scatter graph, but I also want to label each point with a different label (the oil flowrate ... if you must know ;) ).
I cannot figure out how to simply do this without adding each as a new series ... which would be ... time consuming to say the least.
Any ideas?
I have a set of x-y data points (superficial gas and liquid velocities if you must know). Now I want to plot these on an x-y scatter graph, but I also want to label each point with a different label (the oil flowrate ... if you must know ;) ).
I cannot figure out how to simply do this without adding each as a new series ... which would be ... time consuming to say the least.
Any ideas?
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies





RE: Attaching labels to x-y data points on graphs
Peasy
Lemon
Squeezy
Slap this baby into a module, click on the chart in question, run the macro and it'll add the data labels to the left of the x values to each point. I stole it from somewhere.
Sub AttachLabelsToPoints()
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False
'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop
'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter
End Sub
Cheers
Greg Locock
SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Attaching labels to x-y data points on graphs
stopping at this point in the code (Im using excel 2003)
-----
ActiveChart.SeriesCollection(1).points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
-----
Cant see why
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Attaching labels to x-y data points on graphs
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Attaching labels to x-y data points on graphs
http://www.appspro.com/Utilities/ChartLabeler.htm
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Attaching labels to x-y data points on graphs
simply select the series on the chart . . .
or
right mouse button select the series on the chart . . .
select the format data series option . . .
there are several tabs . . .
select the data labels tab and make your selection for data labels.
now labels are shown . . .
for "custom" labels, select the individual label to be "customized".
a highlighted box with box sizing "handles" should be displayed.
type the equal "=" and then select the cell containing the information or reference you desired.
no need to write macros . . .
good luck!
-pmover
RE: Attaching labels to x-y data points on graphs
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Attaching labels to x-y data points on graphs
I am guessing that you put your x data in column A and Y data in column B. (that's the only way I could recreate that error). So an error is generated when the code tries to look to the left of column A. You labels should be in a column to the left of your x data. So your x data can't be in row A.
Sorry if I have jumped to conclusions.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Attaching labels to x-y data points on graphs
Oh, statement of the obvious, it is for xy charts.
Cheers
Greg Locock
SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Attaching labels to x-y data points on graphs
For example, my chart returned the following "series" formula:
"=SERIES(Sheet1!$B$7,Sheet1!$A$8:$A$10,Sheet1!$B$8:$B$10,1)"
The first item in paranetheses is the y label, 2nd item in parantheses is the x range, 3rd item in parantheses is the y range. The trick is to extract the x range from this string.
fwiw, I re-wrote Greg's code in a way that makes a little more sense to me and is perhaps a little more transparent. Still does the same thing. Here is my code if anyone is interested:
CODE
Sub AttachLabelsToPointsRev1()
Dim seriesstring As String ' holds the chart series string
Dim xstring As String ' gives the address of the x series
Dim xstart As Integer, xstop As Integer, xlength As Integer ' give location of xstring within seriesstring
Dim xrange As Range ' range containing x values
Dim labelrange As Range ' range containing labels (assumed to left of xrange)
Dim Counter As Integer ' loop counter
' Extract series string from active chart:
seriesstring = ActiveChart.SeriesCollection(1).Formula
' Extract xrange from series string:
xstart = InStr(seriesstring, ",") + 1
xstop = InStr(xstart + 1, seriesstring, ",") - 1
xlength = xstop - xstart + 1
xstring = Mid(seriesstring, xstart, xlength)
Set xrange = Range(xstring)
' Determine labelrange as range to the left of xrange:
Set labelrange = xrange.Offset(0, -1)
' Apply labels based on labelrange:
For Counter = 1 To labelrange.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel =
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
labelrange(Counter)
Next Counter
End Sub
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Attaching labels to x-y data points on graphs
The chart I want to add the labels to actually has 3 sets of data, the set of data I want to ad the labels to is the 3rd series in columns H and I.
Now I realised how the code worked (it is actually really straight forward) but thought ... the data I want to add the labels to is in column H, so there should be no problems! But gregs code is set up to add the labels to the first series. and the first series is, of course, in columns A and B ... d'oh!
Anyway, now if I set the series to 3 it comes up with a "unable to set the HasDataLabel property of the point class" ... which is bizaare as it is just another x-y series. The code works perfectly well on the first two series, but for some reason it doesnt like the 3rd one (which I want!)
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Attaching labels to x-y data points on graphs
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Attaching labels to x-y data points on graphs
One thing about the last code I posted - underscore missing on the 6th from last line. Should be obvious if you try to run it.
For kicks (?) I did write another version that allows you to pick which range you want to label (1, 2, 3 etc for first y range, 2nd y range, 3rd y range etc), and then select the location of the labels. You can run it more than once to label any/all y series on the chart with a unique set of labels.
CODE
' Modification to previous revisions to allow user to select range for Labels
' Assumptions
' assume that the chart of interest is embedded in the same worksheet as the
' labels and the chart is currently selected
Dim seriesselection As Integer ' holder user's selection of series
' 1 = first y series after x, 2 = 2nd y series after x etc
Dim Counter As Integer ' loop counter
Dim labelrange As Range ' holds the range where labels are stored
Dim thechart As Chart
Set thechart = ActiveChart
Dim thesheet As Worksheet
Set thesheet = ActiveSheet
seriesselection = InputBox("Enter the number of the series you want to label (1,2, 3 etc)")
Set labelrange = Application.InputBox _
(prompt:="Highlight range for labels", _
Title:="range prompt", _
Type:=8)
' Apply labels based on labelrange:
For Counter = 1 To labelrange.Count ' ASSUME there is 1 label per point
ActiveChart.SeriesCollection(seriesselection).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(seriesselection).Points(Counter).DataLabel.Text = _
labelrange(Counter)
Next Counter
End Sub
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Attaching labels to x-y data points on graphs
Cheers
Greg Locock
SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Attaching labels to x-y data points on graphs
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Attaching labels to x-y data points on graphs
Dim thechart As Chart
Set thechart = ActiveChart
Dim thesheet As Worksheet
Set thesheet = ActiveSheet
It all looks very formal, yet you don't use thesheet or thechart later on.
Cheers
Greg Locock
SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Attaching labels to x-y data points on graphs
I had several versions. I thought that selecting the range would take focus away from the chart so I would need another way to refer to the chart once it was deselected (thechart). That did not turn out to be the case - chart remains selected even while select range for input.
The reason for thesheet... at one point I had a problem getting the range inputbox to navigate to the sheet. It kept showing me an inputbox over the top of vba editor that was useless for selecting a range. I tried a few things using sheet reference (like add a default range), but they didn't work. Turns out it was a problem with my syntax.... that range feature doens't work if you use inputbox... you have to use application.inputbox instead. That vestige of my troubleshooting effort was not cleaned up.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Attaching labels to x-y data points on graphs
Cheers
Greg Locock
SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Attaching labels to x-y data points on graphs
BTW if your chart is a separate work sheet (not embeded in a work sheet) you NEED to delete those redundant statements
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Attaching labels to x-y data points on graphs
CODE
Sub AttachLabelsToPointsRev2()
' Modification to previous revisions to allow user to select range for Labels
' Assumptions
' assume that the chart of interest is embedded in the same worksheet as the
' labels and the chart is currently selected
Dim seriesselection As Integer ' holder user's selection of series
' 1 = first y series after x, 2 = 2nd y series after x etc
Dim Counter As Integer ' loop counter
Dim labelrange As Range ' holds the range where labels are stored
Dim seriesstring As String ' holds the chart series string
Dim ystring As String ' gives the address of the x series
Dim xstart As Integer ' give location of xstring within seriesstring
Dim ystart As Integer, ystop As Integer, ylength As Integer ' give location of ystring within seriesstring
Dim yrange As Range ' range containing y values of selected series
' Ask user to select series
seriesselection = InputBox("Enter the number of the series you want to label (1,2, 3 etc)")
' Extract series string from active chart:
seriesstring = ActiveChart.SeriesCollection(seriesselection).Formula
' Extract yrange from series string:
xstart = InStr(seriesstring, ",") + 1 ' xstart is after first comma
ystart = InStr(xstart + 1, seriesstring, ",") + 1 ' ystart is after 2nd comma
ystop = InStr(ystart + 1, seriesstring, ",") - 1 'ystop is before 3rd comma
ylength = ystop - ystart + 1
ystring = Mid(seriesstring, ystart, ylength)
Set yrange = Range(ystring)
' Ask user for labelrange
Set labelrange = Application.InputBox _
(prompt:="Highlight range for labels", _
Title:="range prompt", _
Type:=8)
' Apply labels based on labelrange:
For Counter = 1 To labelrange.Count ' ASSUME there is 1 label per point
If Not Application.IsNA(yrange(Counter)) Then ' Don't label if yrange(counter) is na
ActiveChart.SeriesCollection(seriesselection).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(seriesselection).Points(Counter).DataLabel.Text = _
labelrange(Counter)
End If
Next Counter
End Sub
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Attaching labels to x-y data points on graphs
<Sob>. I loved QP, for its blazing speed and stability.
RE: Attaching labels to x-y data points on graphs
If it's of any use, I changed labelrange(counter) before the last 'If' statement to Int(10 * labelrange(Counter)) / 10# to give one decimal place to the values shown on the graph. There's probably a fancy format statement you can use, but it works.
corus
RE: Attaching labels to x-y data points on graphs
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Attaching labels to x-y data points on graphs
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies