Excel VBA - Add Data Labels from Table body range

Question

I have an Excel worksheet with a table that contains columns for project name, x, y, size in order to create a bubble graph.

I would like it to change the Data labels to be equal to the respective project name but how do I do that? I've searched on the web for solutions but neither of them with succes.

Here's my data:

Chart name: Chart 1
Table name: Table (This one have headers and I'm only searching for the range within the table (data body range).

Here's my code which gives me the error 91:

Sub InsertLabelnameBubble()
    ActiveChart.FullSeriesCollection(1).DataLabels.Select

    For i = 1 To Range("Table").Rows.Count
        ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Select
        Selection.Formula = Range("Table").Cells(i, 1)
    Next i
End Sub

Here's a screenshot of my project.

Where you can see that it uses the Y column (Expected project potential) as the data label text. What I would like it to do is that it should take it from the first column in my table (Project #).

Maybe include some screen shots. Doesn't seem clear what you're asking for. — pgSystemTester
Also, which line are you receiving the error? Error 91 means you are trying to use an object set to Nothing — Kubie
See The VBA Guide To ListObject Excel Tables for how to properly access a table in Excel VBA. You must probably use something like ActiveSheet.ListObjects("Table").DataBodyRange(i, 1) — Pᴇʜ
Am I allowed to upload my project file somewhere and post the link here? I'm not sure a screenshot is enough, and I'm really searching for a solution, which should be included in my Master's thesis. — Rasmus Holt Hansen
You could manually link your labels to cells. Use a formula to pull the correct project name into a specific cell and then click your label twice before selecting the formula bar and then your cell. John Peltier explains it on his site - third heading. — Darren Bartrup-Cook

Bojan Goldy Bojan Goldy · Accepted Answer · 2018-10-31T15:18:52

This is code that I use for data labels from a range. Have found this on stackoverflow a while back:

Sub DataLables 
    Dim ws as worksheet, DataLR As Series, pts As Points, pt As Point, rngLabels As Range, IDi As Integer, ChtObj As ChartObject
    Set ws = ActiveWorkbook.ActiveSheet 

    With ws
    Set ChtObj = .ChartObjects("ChatName")
    Set rngLabels = .Range("A5:A39")
            Set DataLR = ChtObj.Chart.SeriesCollection(2)
            DataLR.HasDataLabels = True  
         For Each pt In pts
         IDi = IDi + 1
             pt.DataLabel.Text = rngLabels.Cells(IDi).Text
             pt.DataLabel.Font.Bold = True
         Next pt    
    End With

End Sub

Let me know if this helps. It will update the data labels with the values in the range.

Excel VBA - Add Data Labels from Table body range

2 Answers