VBA - Auto label graph with data labels

[joek128]

Hello,

Looking for a way to fix labels to dynamic chart objects. The example below:

Two dynamic series (the red bubbles and green bubbles) with the size representing the contribution to the portfolio. Looking to write some form of code that will take the Ticker Symbols from Column A (for data in Series A - Green Bubbles) and Column B (for data in Series B - Red Bubbles)and auto update the labels to each series. Any idea how to do this? Thanks!

http://imgur.com/XZiIJma

 

[Domenic]

I haven't looked at what you have in your link, but hopefully this will do what you want. Note that it doesn't include any error handling, and that it's based on the following assumptions...

1) Sheet1, Column A and B, starting at Row 2, contain the ticker symbols to be used for the data labels.

2) Sheet2 contains the chart, and the chart is named "Chart 1".

3) The labels are to be position below each data point.

Option Explicit

Sub AddLabelsToChart()

    Dim rLabels As Range
    Dim LastRow As Long
    Dim SeriesIndex As Long
    Dim PointIndex As Long
    
    With Worksheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If LastRow < 2 Then
            MsgBox "No data is available.", vbInformation
            Exit Sub
        End If
        Set rLabels = .Range("A2:B" & LastRow)
    End With
    
    With Worksheets("Sheet2").ChartObjects("Chart 1").Chart
        For SeriesIndex = 1 To 2
            With .SeriesCollection(SeriesIndex)
                For PointIndex = 1 To .Points.Count
                    With .Points(PointIndex)
                        .HasDataLabel = True
                        With .DataLabel
                            .Text = rLabels.Cells(PointIndex, SeriesIndex).Text
                            .Position = xlLabelPositionBelow
                        End With
                    End With
                Next PointIndex
            End With
        Next SeriesIndex
    End With
    
    MsgBox "Labels have been added.", vbInformation
    
End Sub

Hope this helps!