+ Reply to Thread
Results 1 to 3 of 3

How to add with macro data labels to bubble chart? Multiple series and not series names.

  1. #1
    Registered User
    Join Date
    02-19-2017
    Location
    Mechelen, Belgium
    MS-Off Ver
    2016
    Posts
    2

    Angry How to add with macro data labels to bubble chart? Multiple series and not series names.

    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.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How to add with macro data labels to bubble chart? Multiple series and not series name

    First, please use code tags around code.

    Second, you asked this question in other online forums, too. Post links to these questions so people don't waste their time here if your question has already been answered elsewhere.

  3. #3
    Registered User
    Join Date
    02-19-2017
    Location
    Mechelen, Belgium
    MS-Off Ver
    2016
    Posts
    2

    Re: How to add with macro data labels to bubble chart? Multiple series and not series name

    Please Login or Register  to view this content.


    Please Login or Register  to view this content.



    thanks for the advise

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Bubble Chart with 2 Series
    By aaallday in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-01-2015, 04:55 AM
  2. [SOLVED] Align Line Chart's data series Data labels on top of Bar chart data series
    By rgunlimited in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-01-2014, 10:38 AM
  3. Bubble chart - help with macro set up for auto adding series
    By cjuba in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-31-2014, 07:31 AM
  4. Add labels to a bubble chart with multiple series
    By wahlberg in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 01-19-2013, 10:19 PM
  5. Bubble Chart - Multiple Series
    By David Brown in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-19-2011, 12:33 PM
  6. Add Data Labels, Show Series Names
    By knightcloud in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2011, 01:32 AM
  7. Chart series with some data labels
    By robotmannick in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-11-2006, 08:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1