VBA EXCEL 2016 - How to add labels to points in bubble chart automatically? multiple series, Names of values in cells.

T

Tanakorn

New Member
Joined
Feb 19, 2017
Messages
1
Hi guys


So I have multiple series but for each point in the serie i want to have a different name. In excel i can select these names but after closing the file en re-opening again, i will have to "refresh" all the series before i can enter new point in a serie.


my code so far but it keeps being stuck on the first serie, the graph only shows the data labels of the first serie.




Sub CreateDataLabels()


'variables for looping over chart objects
Dim BrainstormChart As Chart
Dim Categories As Series

'variables for looping over cells
Dim SingleCell As Range
Dim List As Range

'variable to keep track of number of ideas
Dim IdeaCounter As Integer


IdeaCounter = 1


Set List = Worksheets("Brainstorm Input").Range("C5", "C79")
Set BrainstormChart = ActiveSheet.ChartObjects("Grafiek 1").Chart

'loop over each data series and enable data labels
For Each Categories In BrainstormChart.SeriesCollection
Categories.HasDataLabels = True
Next Categories

'loop over each cell in the list of source data
For Each SingleCell In List
'loop over each series in the chart
For Each Categories In BrainstormChart.SeriesCollection
'change the label text to be the idea's name
Categories.Points(IdeaCounter).DataLabel.Text = SingleCell.Value
On Error Resume Next
Next Categories

IdeaCounter = IdeaCounter + 1
Next SingleCell

End Sub








What i have to do every single time when opening the file is this (i recorded my steps as a macro) :


Sub Brainstormrefresh()
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.SetElement (msoElementDataLabelCenter)
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(5).DataLabels.Select
ActiveChart.FullSeriesCollection(5).DataLabels.Select
ActiveChart.SeriesCollection(5).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$28:$C$36",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(5).DataLabels.Select
Selection.ShowValue = False
Selection.AutoText = True
ActiveChart.FullSeriesCollection(4).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(4).DataLabels.Select
ActiveChart.SeriesCollection(4).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$21:$C$27",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(4).DataLabels.Select
Selection.AutoText = True
ActiveChart.FullSeriesCollection(3).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(3).DataLabels.Select
ActiveChart.SeriesCollection(3).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$12:$C$20",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(3).DataLabels.Select
Selection.AutoText = True
ActiveChart.FullSeriesCollection(2).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(2).DataLabels.Select
ActiveChart.SeriesCollection(2).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$5:$C$11", 0
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(2).DataLabels.Select
Selection.AutoText = True
ActiveChart.FullSeriesCollection(6).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(6).DataLabels.Select
ActiveChart.SeriesCollection(6).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$37:$C$42",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(6).DataLabels.Select
Selection.AutoText = True
ActiveChart.FullSeriesCollection(7).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(7).DataLabels.Select
ActiveChart.SeriesCollection(7).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$43:$C$49",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(7).DataLabels.Select
Selection.AutoText = True
ActiveChart.FullSeriesCollection(8).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(8).DataLabels.Select
ActiveChart.SeriesCollection(8).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$50:$C$55",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(8).DataLabels.Select
Selection.AutoText = True
ActiveChart.FullSeriesCollection(9).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(9).DataLabels.Select
ActiveChart.SeriesCollection(9).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$56:$C$61",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(9).DataLabels.Select
Selection.AutoText = True
ActiveChart.FullSeriesCollection(10).Select
ActiveChart.FullSeriesCollection(10).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(10).DataLabels.Select
ActiveChart.SeriesCollection(10).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$62:$C$67",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(10).DataLabels.Select
Selection.AutoText = True
ActiveChart.FullSeriesCollection(11).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(11).DataLabels.Select
ActiveChart.SeriesCollection(11).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$68:$C$73",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(11).DataLabels.Select
Selection.AutoText = True
ActiveChart.FullSeriesCollection(12).DataLabels.Select
Selection.ShowValue = False
ActiveChart.FullSeriesCollection(12).DataLabels.Select
ActiveChart.SeriesCollection(12).DataLabels.Format.TextFrame2.TextRange. _
InsertChartField msoChartFieldRange, "='Brainstorm Input'!$C$74:$C$79",
Selection.ShowRange = True
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.FullSeriesCollection(12).DataLabels.Select
Selection.AutoText = True
Range("U16").Select
End Sub








Can someone help me pls? Also the recorde macro doesn't work when i play it.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You must log in or register to reply here.

Similar threads

R
  • Solved
How do I extract the data label of a trendline in VBA?
2
Replies
14
Views
988
RockandGrohl
R
T
  • Question
Excel VBA/Macro Error
Replies
1
Views
980
mart37
M
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
176
dilshod_k
D
V
  • Question
VBA: Custom Data Labels Question
Replies
0
Views
2K
vlad7984
V
V
  • Question
VBA: data label text is being cut off
Replies
5
Views
4K
vlad7984
V

Forum statistics

Threads
1,215,768
Messages
6,126,783
Members
449,336
Latest member
p17tootie

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top