I'm trying to make a macro that will change the data labels on a scatter chart to the title of each point. Ej:
In this case I would like the data labels to show the movie title. The code I used comes from this website, but my scatter chart has two series of data. I would like to use dynamic named ranges to tell the macro the range of cells where the names are, in my code the dynamic named ranges are FONames & BONames. Here's my code:
<code style="margin: 0px; padding: 0px; border: 0px; font-size: 13.333333969116211px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Sub CreateDataLabels()
'holds the entire film data series in the chart
Dim FilmDataSeries As Series
Dim FilmDataSeries2 As Series
'holds one cell at a time
Dim SingleCell As Range
'holds the full list of cells containing film names
Dim FilmList As Range
Dim FilmList2 As Range
'keeps track of which datapoint we're labelling
Dim FilmCounter As Integer
Dim FilmCounter2 As Integer
'The sheet in which the graph is on
Dim ws As Worksheet
Set ws = Worksheets("Datos Gráfico")
'set the counter to start at 1
FilmCounter = 1
FilmCounter2 = 1
'set a reference to the cells containing the list of films
Set FilmList = Range("FONames")
Set FilmList2 = Range("BONames")
'set a reference to the chart data series
Set FilmDataSeries = ws.ChartObjects(1).Chart.SeriesCollection(1)
Set FilmDataSeries2 = ws.ChartObjects(1).Chart.SeriesCollection(2)
'make sure data labels are turned on
FilmDataSeries.HasDataLabels = True
FilmDataSeries2.HasDataLabels = True
'loop over the cells in the list of films
For Each SingleCell In FilmList
FilmDataSeries.Points(FilmCounter).DataLabel.Text = SingleCell.Value
FilmCounter = FilmCounter + 1
Next SingleCell
For Each SingleCell In FilmList2
FilmDataSeries2.Points(FilmCounter2).DataLabel.Text = SingleCell.Value
FilmCounter2 = FilmCounter2 + 1
Next SingleCell
End Sub
</code>
I'm getting a 1004 error saying the dimension specified is not valid. When I replace FONames or BONames with an actual range of cells, Ej. Range("A5","A11"), everything works perfectly. How do I use those dynamic named ranges in my code?
In this case I would like the data labels to show the movie title. The code I used comes from this website, but my scatter chart has two series of data. I would like to use dynamic named ranges to tell the macro the range of cells where the names are, in my code the dynamic named ranges are FONames & BONames. Here's my code:
<code style="margin: 0px; padding: 0px; border: 0px; font-size: 13.333333969116211px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Sub CreateDataLabels()
'holds the entire film data series in the chart
Dim FilmDataSeries As Series
Dim FilmDataSeries2 As Series
'holds one cell at a time
Dim SingleCell As Range
'holds the full list of cells containing film names
Dim FilmList As Range
Dim FilmList2 As Range
'keeps track of which datapoint we're labelling
Dim FilmCounter As Integer
Dim FilmCounter2 As Integer
'The sheet in which the graph is on
Dim ws As Worksheet
Set ws = Worksheets("Datos Gráfico")
'set the counter to start at 1
FilmCounter = 1
FilmCounter2 = 1
'set a reference to the cells containing the list of films
Set FilmList = Range("FONames")
Set FilmList2 = Range("BONames")
'set a reference to the chart data series
Set FilmDataSeries = ws.ChartObjects(1).Chart.SeriesCollection(1)
Set FilmDataSeries2 = ws.ChartObjects(1).Chart.SeriesCollection(2)
'make sure data labels are turned on
FilmDataSeries.HasDataLabels = True
FilmDataSeries2.HasDataLabels = True
'loop over the cells in the list of films
For Each SingleCell In FilmList
FilmDataSeries.Points(FilmCounter).DataLabel.Text = SingleCell.Value
FilmCounter = FilmCounter + 1
Next SingleCell
For Each SingleCell In FilmList2
FilmDataSeries2.Points(FilmCounter2).DataLabel.Text = SingleCell.Value
FilmCounter2 = FilmCounter2 + 1
Next SingleCell
End Sub
</code>
I'm getting a 1004 error saying the dimension specified is not valid. When I replace FONames or BONames with an actual range of cells, Ej. Range("A5","A11"), everything works perfectly. How do I use those dynamic named ranges in my code?
Last edited:
