BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andrew Gould on 30 May 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Using VBA to Label Multiple Series in a Chart
A while ago I wrote a blog describing how you could use VBA in Excel to label the data points in a scatter chart. Recently someone asked a question about how to adapt the code I created to label more than one series in the same chart. Here's the problem we're trying to solve:
The chart contains one data series plotting RunTime against Budget and another plotting RunTime against Box Office. We want to label each data point with the name of the film.
The Code to Solve the Problem
The key to this solution is looping over each data series for every data point that we want to label. The code below will work for any number of data series that the chart contains:
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
FilmCounter = 1
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
'loop over each cell in the list of source data
For Each SingleCell In FilmList
'loop over each series in the chart
For Each FilmDataSeries In FilmChart.SeriesCollection
'change the label text to be the film's name
FilmDataSeries.Points(FilmCounter).DataLabel.Text = SingleCell.Value
Next FilmDataSeries
FilmCounter = FilmCounter + 1
Next SingleCell
End Sub
The End Result
The end result of running this code is that each data point in the chart will be labelled:
Each data point in the chart now shows the name of the film that it represents.
You can download the completed example for this blog.