how to add data label to bubble chart in excel

Question

Hi I want to add customize data label to my bubble chart. my code is below. at the moment data label refer to XValues. I would like my data label fill with bubble size. would u mind help me how I can customize below code?

I tried to add .DataLabel.Text = "txt" but I received below error: Run-time error '438':Object doesn't support this property or method

Public Sub CreateMultiSeriesBubbleChart()
If (Selection.Columns.Count <> 4 Or Selection.Rows.Count < 3) Then
    MsgBox "Selection must have 4 columns and at least 2 rows"
    Exit Sub
End If

Dim red, green, blue As Integer


Dim bubbleChart As ChartObject
Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=Selection.Left, Width:=600, Top:=Selection.Top, Height:=400)
bubbleChart.Chart.ChartType = xlBubble
Dim r As Integer

For r = 2 To Selection.Rows.Count
    With bubbleChart.Chart.SeriesCollection.NewSeries
        .Name = "=" & Selection.Cells(r, 1).Address(External:=True)
        .XValues = Selection.Cells(r, 2).Address(External:=True)
        .Values = Selection.Cells(r, 3).Address(External:=True)
        .BubbleSizes = Selection.Cells(r, 4).Address(External:=True)
        .Format.Fill.Solid
        .Format.Fill.ForeColor.RGB = RGB(61, 161, 161)
     '   .DataLabel.Text = "txt"
    End With

Next

bubbleChart.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
bubbleChart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 2).Address(External:=True)

bubbleChart.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
bubbleChart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 3).Address(External:=True)

bubbleChart.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
bubbleChart.Chart.Axes(xlCategory).MinimumScale = 0

End Sub

My input sample:

Label          Hour Day count
01-SUNDAY       14  1   1
01-SUNDAY       19  1   1
02-MONDAY       12  2   1
02-MONDAY       13  2   1
02-MONDAY       14  2   2
02-MONDAY       16  2   2

Answer 1

Without using VBA, right click on the bubbles and select Add Data Labels. Then, right click on the data labels and click Format Data Labels. Under Label Options, select Value From Cells and specify the cells containing the labels you'd like to use.

Answer 2

DataLabel.Text is a method for a Point, not the NewSeries

This code:

For r = 2 To Selection.Rows.Count
    With bubbleChart.Chart.SeriesCollection.NewSeries
        [...]
        .DataLabel.Text = "txt"
    End With 
Next

...attempts to label the series, and fails.

Recognizing this code as being from another famous example of "multi-series Bubble Charts", it is a logical assumption that we only need to handle 1 data point per series, which makes the following code the solution:

For r = 2 To Selection.Rows.Count
    With bubbleChart.Chart.SeriesCollection.NewSeries
        [...]
        .Points(1).HasDataLabel = True
        .Points(1).DataLabel.Text = "txt"
    End With 
Next