Trying to Create Custom Range of Label Names for XY Scatterplot

[mp1021257]

Hi,

I have data in three columns. In column A, I have a list of stock tickers. In column B I have a list of percentages, and in column C, I have a list of numbers (returned by a vlookup formula). I have created an XY scatter plot of column B and C. I would like to label the XY scatterplot datapoints using the labels in column A. Is this possible?

My same data set looks something like this:

Ticker	%	Number
ABC	56%	5.0
CED	54%	5.1
EFG	52%	4.2
HIG	51%	6.0

<tbody>
</tbody>

Thank you for the help.

 

[PaddyD]

Hi.

Install this:

The XY Chart Labeler Add-in

..and your problems will go away

 

[mp1021257]

Do you have the underlying code? I am unable to run an application from an unknown source on my work computer.

Thanks,
Mark

Hi.

Install this:

The XY Chart Labeler Add-in

..and your problems will go away

 

[Domenic]

This macro adds data labels to the active XY Chart. First, though, it does the following...

1) It makes sure that a chart has been selected and is active.

2) It makes sure that the active chart is an XY Chart.

3) It makes sure that the chart contains a series.

4) It looks for the data labels in the column to the left of the X values (or the row above the X values if the chart data is transposed).

5) It adds the data labels above the data points.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] AddDataLabelsFromRangeToXYChart()

    [COLOR=darkblue]Dim[/COLOR] oChart [COLOR=darkblue]As[/COLOR] Chart
    [COLOR=darkblue]Dim[/COLOR] oSeries [COLOR=darkblue]As[/COLOR] Series
    [COLOR=darkblue]Dim[/COLOR] oDataLabel [COLOR=darkblue]As[/COLOR] DataLabel
    [COLOR=darkblue]Dim[/COLOR] rXVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rLabels [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] sFormula [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sXValsAddr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] bIsXYChart [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] oChart = ActiveChart
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] oChart [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] oChart.ChartType
            [COLOR=darkblue]Case[/COLOR] -4169, 72, 73, 74, 75 [COLOR=green]'XY chart types[/COLOR]
                bIsXYChart = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] bIsXYChart [COLOR=darkblue]Then[/COLOR]
        MsgBox "Select an XY Chart, and try again.", vbExclamation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] oSeries = oChart.SeriesCollection(1)
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    [COLOR=darkblue]If[/COLOR] oSeries [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        MsgBox "No chart series found.", vbExclamation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    sFormula = oSeries.Formula
    
    sXValsAddr = Split(s[COLOR=darkblue]For[/COLOR]mula, ",")(1)
    
    [COLOR=darkblue]Set[/COLOR] rXVals = Range(sXValsAddr)
    
    [COLOR=darkblue]If[/COLOR] rXVals.Columns.Count = 1 [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] rXVals.Column = 1 [COLOR=darkblue]Then[/COLOR]
            MsgBox "Make sure that the data labels are located one column" & vbNewLine & "to the left of the 'X' column.", vbExclamation
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rLabels = rXVals.Offset(, -1)
    [COLOR=darkblue]Else[/COLOR]
        [COLOR=darkblue]If[/COLOR] rXVals.Row = 1 [COLOR=darkblue]Then[/COLOR]
            MsgBox "Make sure that the data labels are located one row" & vbNewLine & "above the 'X' row.", vbExclamation
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rLabels = rXVals.Offset(-1)
    [COLOR=darkblue]End[/COLOR] If
    
    [COLOR=darkblue]With[/COLOR] oSeries
        For i = 1 [COLOR=darkblue]To[/COLOR] .Points.Count
            .Points(i).HasDataLabel = [COLOR=darkblue]True[/COLOR]
            [COLOR=darkblue]Set[/COLOR] oDataLabel = .Points(i).DataLabel
            [COLOR=darkblue]With[/COLOR] oDataLabel
                .Text = "=" & rLabels.Cells(i).Address(External:=True)
                .Position = xlLabelPositionAbove
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[mp1021257]

This is great. Thanks!

 

[Domenic]

You're very welcome!

Cheers!