The Problem With Labelling the Data Points in an Excel Scatter Chart

I've posted a solution here!

I see what you mean, but that wouldn't work for this example because the names of the films aren't contained within any of the chart data series.  The two series in the chart are the Run Time and the Budget and we could display those as data labels without having to use VBA at all.  The only reason we're using VBA here is to label the chart with values that are stored in cells rather than in the chart itself.

Hi Alex,

 

Thanks for the comment.  You’re right about the labels in my example repeating – the idea is that each film name has multiple data points associated with it, so you’d expect to see each film appear more than once.  The counter doesn’t actually reset however, and the list of films is looped over only once.  What happens is that for each film in the list, every data series in the chart is looped over and a single data point in each series is labelled.  So all the data points associated with one film are labelled before moving on to the next.

 

For your example it depends on exactly how you have your data arranged.  It sounds as though you have a list containing one column of names that you want to use as labels, next to one or more columns of values you’re using as data points.  Your single list is used to create multiple data series in the chart, with each series using a different number of rows from the list.

 

If that’s the case then we need to loop over the collection of cells containing the labels just once to add a label to each data point.  The problem you’ve got is knowing when you switch from one data series to the next.  One solution would be to keep track of the number of data points we’ve labelled and check to see if we’ve exceeded the number of data points in the current data series.  If that happens we can reset our counter and move on to the next data series.  We’ll also need to keep track of the number of data series we’ve labelled.  The code below should do the trick, assuming all of the above:

Sub CreateDataLabels()

    'variables for looping over chart objects
    Dim FilmChart As Chart
    Dim FilmDataSeries As Series
   
    'variables for looping over cells
    Dim SingleCell As Range
    Dim FilmList As Range
   
    'variable to keep track of number of films
    Dim FilmCounter As Integer
   
    'variable to keep track of number of series
    Dim SeriesCounter As Integer
   
    Set FilmList = Range("A2", "A11")
    Set FilmChart = ActiveSheet.ChartObjects(1).Chart
   
    'loop over each data series and enable data labels
    For Each FilmDataSeries In FilmChart.SeriesCollection
        FilmDataSeries.HasDataLabels = True
    Next FilmDataSeries
   
    FilmCounter = 1
    SeriesCounter = 1
   
    Set FilmDataSeries = FilmChart.SeriesCollection(SeriesCounter)
   
    'loop over each cell in the list of source data
    For Each SingleCell In FilmList
       
        'change the label text to be the film's name
        FilmDataSeries.Points(FilmCounter).DataLabel.Text = SingleCell.Value
       
        FilmCounter = FilmCounter + 1
       
        'if the FilmCounter is higher than the number of points in the series, reset the FilmCounter
        'move on to the next data series
        If FilmCounter > FilmDataSeries.Points.Count Then
            FilmCounter = 1
            SeriesCounter = SeriesCounter + 1
           
            'if we've exceeeded the number of data series in the chart then exit the loop
            If SeriesCounter > FilmChart.SeriesCollection.Count Then Exit For
           
            'if we're still in the loop, store a reference to the next data series
            Set FilmDataSeries = FilmChart.SeriesCollection(SeriesCounter)
           
        End If
       
    Next SingleCell
   
End Sub

Yep, that's pretty much it!  Don't forget to declare a new variable to hold each single chartobject as you process the loop.  You'll also want to grab a reference to the chart part of each chartobject as you go through the loop.  I've marked the NEW and EDITED lines with comments below:

Sub CreateDataLabels()

    Dim FilmChart As Chart
    Dim FilmDataSeries As Series
   
    '***NEW LINE***
    Dim SingleChartObject As ChartObject
   
    Dim SingleCell As Range
    Dim FilmList As Range
   
    Dim FilmCounter As Integer
    Dim SeriesCounter As Integer
   
    Set FilmList = Range("A2", "A11")
   
    '***NEW LINE***
    For Each SingleChartObject In ActiveSheet.ChartObjects
       
        '***EDITED LINE***
        Set FilmChart = SingleChartObject.Chart
       
        For Each FilmDataSeries In FilmChart.SeriesCollection
            FilmDataSeries.HasDataLabels = True
        Next FilmDataSeries
       
        FilmCounter = 1
        SeriesCounter = 1
       
        Set FilmDataSeries = FilmChart.SeriesCollection(SeriesCounter)
       
        For Each SingleCell In FilmList
           
            FilmDataSeries.Points(FilmCounter).DataLabel.Text = SingleCell.Value
           
            FilmCounter = FilmCounter + 1
           
            If FilmCounter > FilmDataSeries.Points.Count Then
                FilmCounter = 1
                SeriesCounter = SeriesCounter + 1
               
                If SeriesCounter > FilmChart.SeriesCollection.Count Then Exit For
               
                Set FilmDataSeries = FilmChart.SeriesCollection(SeriesCounter)
               
            End If
           
        Next SingleCell
       
    '***NEW LINE***
    Next SingleChartObject
   
End Sub

Hi,

Yep, you should be able to change the format of the date using the Format function. Try setting the DataLabel.Text property to be equal to

Format(SingleCell.Value, "mmm-yy")

Hope that helps!

Sorry you're having issues!  As a starting point for resolving the problem could you download the completed example from the bottom of Part 5 of the series and just check that you can run the code in there without receiving the error message.  When you've done that get back to me and we'll see what else we can do!