Attaching labels to x-y data points on graphs 4

[ColourfulFigsnDiags]

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?

Read the Eng-Tips Site Policies at FAQ731-376

 

[GregLocock]

Easy
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

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[ColourfulFigsnDiags]

Thanks but I get an application-defined or object-defined error (run time error 1004).

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

 

[ColourfulFigsnDiags]

Hmmm has something to do with the label text ... i.e. this bit " Range(xVals).Cells(Counter, 1).Offset(0, -1).Value"

Read the Eng-Tips Site Policies at FAQ731-376

 

[joerd]

Try Rob Bovey's Chart Labeler:

http://www.appspro.com/Utilities/ChartLabeler.htm

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[pmover]

geez . . .

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

 

[joerd]

Right, that turns out to be quite a lot of work if you don't want the X or Y values displayed but something else.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[electricpete]

That is some interesting code from Greg. Works like a charm with no effort.

(Thanks but I get an application-defined or object-defined error (run time error 1004).

stopping at this point in the code (Im using excel 2003)

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.

 

[GregLocock]

It is rather neat code. I almost understand how it does what it does. Quite why a labelled xy chart isn't a built in chart type has always baffled me.

Oh, statement of the obvious, it is for xy charts.

Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[electricpete]

I spent awhile trying to figure out Greg's code. It makes sense, once you examine what is contained in the string returned by ActiveChart.SeriesCollection(1).Formula

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:

Option Explicit

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.

 

[ColourfulFigsnDiags]

electricpete ... I feel like walking into a room and saying "Hello, my name is James, and I am an unmitigated moron" ...

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

 

[ColourfulFigsnDiags]

Done, fixed (just deleted the series and started again ... voila), looks perfect, stars awarded, many thanks Greg and Pete! ... brilliant thanks all!

Read the Eng-Tips Site Policies at FAQ731-376

 

[electricpete]

Glad you got it working.

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.

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 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.

 

[GregLocock]

Excellent. Have a beer. Now we have all the functionality of Quattro Pro circa 1992.





Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[electricpete]

yippee!


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[GregLocock]

OK, what does this actually do?

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

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[electricpete]

The bottom line, in this version, it does nothing.

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.

 

[GregLocock]

That's why I try and distribute compiled code only. Then you never have to explain!


Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[ColourfulFigsnDiags]

Ahhh I figured out why I had the problem with the "unable to set the HasDataLabel property of the point class" ... it was because some of my data points were listed as #N/A so there was no data point associated with the counter value. Is there any way to first check the existance of the point and if it exists only then apply the label.

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

 

[electricpete]

I added a check whether the cell contains na. If na is contained, it skips that cell label and continues the remaining in proper order.

Option Explicit

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.