Qfyilyi

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

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.

So I did find the solution myself. Here's my final code inserting labels from the Table into the Bubble Chart:

Sub DataLables()



  ActiveSheet.ChartObjects("Chart 1").Activate

  ActiveChart.FullSeriesCollection(1).DataLabels.Select



  For i = 1 To Range("Table1[Project '#]").Count



    ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Select



    Selection.Formula = Range("Table1[Project '#]").Cells(i, 1)



  Next i



End Sub