0

I'm creating a bubble chart that includes the code below. It creates the chart correctly with all the expected data... except in some cases, the labels just aren't showing. I've sorted and added values and checked the range of data and whatnot, but there doesn't seem to be any reason why some labels show and some don't.

I have a few screenshots include one that shows the values are selected with the range and that isn't showing, and that data point is in the range. It was hard to get all the screenshots, but the third screenshot is showing Co4 missing even though the values are pointed in the data label properties.

Any experience fixing an issue like this?

.DataLabels.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, "='Active Charts'!$P$18:$P$" & rgMap.Offset(-1, 0).Row, 0

Chart with circled errors data table example enter image description here

I checked out these entries but it didn't seem to be relevant all labels missing, sunburst chart label issue

Jason Torpy
  • 39
  • 2
  • 9
  • You're referencing `"='Active Charts'!$P$18:$P$"blah blah` but you don't tell use what rows and columns are in the screenshot. And that one line of code you posted is pretty lonely; what's the rest of the code look like? – Jon Peltier Sep 22 '20 at 00:34
  • I don't see the button to edit and I can't post that much code in this comment. But I am giving the chart a range of data to assign the points to. And it normally works, but they seem to 'attrit' over time so eventually not all of them show up. What I noticed also is that some charts have 'very color by point' in the format data label > fill menu, but bubble charts don't have that option. That wouldn't matter, but I'm trying to redraw the chart every time and it doesn't seem to let me do that... – Jason Torpy Sep 23 '20 at 02:18
  • Issuing the chart fields approach to labeling points does have a problem. If for some reason a point is not plotted (e.g., a blank cell for x or y value), the label doesn't show, of course, but then or never comes back even when the point is plotted again. – Jon Peltier Sep 24 '20 at 11:51
  • Since you're plotting one point per series, make the cell with the label Co1 etc the series name, and include that in the data label. Much more reliable than using a label from an arbitrary range. – Jon Peltier Sep 24 '20 at 11:53
  • that makes sense. it's just that the range has to be able to change. the problems crop up as the labels are there, then one of them feeds blank and the labels drop off, then I can't add it back.some (ugly) chart code is below. `With Sheets("Active Charts").ChartObjects("Chart 8").Chart.FullSeriesCollection(1) .XValues = "='Active Charts'!$Q$18:$Q$" & rgMap.Offset(-1, 0).Row & rgMap ' Attractiveness .Values = "='Active Charts'!$R$18:$R$" & rgMap.Offset(-1, 0).Row ' Achievability .BubbleSizes = "='Active Charts'!$S$18:$S$" & rgMap.Offset(-1, 0).Row ' MM USD End With` – Jason Torpy Sep 24 '20 at 20:52
  • What I meant was, don't use the chart fields approach, just the regular data labels approach. Set the ShowSeriesName to true and the others as false. AFK, but I'll try to cycle back with some real code. – Jon Peltier Sep 25 '20 at 21:40

1 Answers1

0

Finally at my computer with a few minutes.

Here's a very old piece of code that I've adjusted for Excel 2013+.

Put data into four columns in this order: Name, X, Y, and Z (Bubble Size).

The following code creates a bubble chart with this data, ignoring any non data rows (header rows or rows with a blank for X, Y, or Z). It makes a separate series for each row, uses the first column for the name of the one-point series, then applies a label with the series name and bubble size.

Sub OneRowPerBubbleSeries()
    '' Takes 4-column range and constructs Bubble chart
    '' Uses one series per row: Columns in order: Name, X, Y, Z
    Dim wks As Worksheet
    Dim cht As Chart
    Dim srs As Series
    Dim rng As Range
    Dim rng1 As Range
    Dim rownum As Integer
    Dim bFirstRow As Boolean
    Set wks = ActiveSheet
    Set rng = Selection
    Set cht = wks.Shapes.AddChart2(269, xlBubble3DEffect).Chart
    bFirstRow = True
    For rownum = 2 To rng.Rows.Count
        Set rng1 = rng.Cells(rownum, 2).Resize(1, 3)
        If IsNumeric(rng1.Cells(1, 1).Value) And _
                IsNumeric(rng1.Cells(1, 2).Value) And _
                IsNumeric(rng1.Cells(1, 3).Value) Then
            '' First time: need to do it differently
            If bFirstRow Then
                cht.SetSourceData Source:=rng1, PlotBy:=xlColumns
                bFirstRow = False
            Else
                Set srs = cht.SeriesCollection.NewSeries
            End If
            With cht.SeriesCollection(cht.SeriesCollection.Count)
                .Values = rng1.Cells(1, 2)
                .XValues = rng1.Cells(1, 1)
                .BubbleSizes = "=" & rng1.Cells(1, 3).Address _
                    (ReferenceStyle:=xlR1C1, External:=True)
                .Name = rng.Cells(rownum, 1)
                .HasDataLabels = True
                With .DataLabels
                  .Position = xlLabelPositionRight
                  .ShowSeriesName = True
                  .ShowValue = False
                  .ShowBubbleSize = True
                  .NumberFormat = "$#,##0"
                End With
            End With
        End If
    Next
End Sub
Jon Peltier
  • 5,895
  • 1
  • 27
  • 27