Adding Labels to Scattergraphs

pwchad

New Member
Joined
Jan 31, 2005
Messages
6
Hi,

I've been trying to teach myself VBA, with mixed results.

I want to label the points on a scattergraph - Excel doesn't seem to do it automatically.
So I tried the code below to add labels from a range of 52 labels, arranged in a column.

When I run it it doesn't like:

Code:
Cells(a, 5).Select

any ideas?
I have found an add-in that does this much better, but I would like to understand what I'm doing wrong

Thanks for your help!!!
pwchad

Code:
Sub AddValues()

Dim a As Integer
Dim strName As String
a = 1

For a = 1 To 52
Cells(a, 5).Select
strName = Cells(a, 5).FormulaR1C1

ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.SeriesCollection(1).Points(a).ApplyDataLabels Type:= _
        xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
    ActiveChart.SeriesCollection(1).Points(a).DataLabel.Characters.Text = strName
Next a

End Sub
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Alisa

Board Regular
Joined
Jan 28, 2005
Messages
97
I'm not sure about your code, but excel does label series - in the chart, right click on the series that you want to label and go to 'format data series'. Then click on the 'data labels' tab. There are some options there for what to include in the label.
good luck, Alisa
 

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
Doesn't seem to be any need to select the cells.

Maybe try this as an alternative (I don't have your data, but it seems that you start with cell E1):

Code:
Sub test()
    Dim Ser As Series
    Dim Pt As Point
    Dim Counter As Long
    Set Ser = ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(1)
    Ser.HasDataLabels = True
    Counter = 1
    For Each Pt In Ser.Points
        Pt.DataLabel.Text = Range("E1").Offset(Counter, 0)
        Counter = Counter + 1
    Next Pt
End Sub
 

pwchad

New Member
Joined
Jan 31, 2005
Messages
6
Shades,

Thank you for guiding me further along the learning curve.
The code works perfectly!!

Cheers

:biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,378
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top