Share via

Excel VBA bug with DataLabels.ShowSeriesName; it tacks three extra characters on ends of labels.

Anonymous
2017-08-09T19:38:51+00:00

This is with Excel 2016 or Office 365 for corporate.

When I use this command everything works great except that I wind up with an extra comma, space, and hyphen (", -") at the end of each label.

In one case there is a comma, space, and a zero.

The series names link to cells in the spreadsheet. 

Those extra characters are nowhere in the original text in the cells.

Is this an Excel VBA bug?

Or is there a switch to turn off the bad characters?

Here is my code, it actually changes the colors of the chart series according to a selected block of cells, adds the series name as a data label, and has that label match the color of the chart series.  Everything works great except for the stray text at the end of each label.

Sub ColorChartSeries()

    Selection.Name = "MLF_DATA_BLOCK"

    ActiveSheet.ChartObjects("Chart 1").Activate

    Rows1 = Range("MLF_DATA_BLOCK").Rows.Count

    Debug.Print "rows = "; Rows1

    For MLF_ROW = 1 To Rows1

        Range("MLF_DATA_BLOCK").Item(MLF_ROW, 1).Select

        mlfColor = Selection.Interior.Color

        Debug.Print "color = "; mlfColor

        ActiveSheet.ChartObjects("Chart 1").Activate

        ActiveChart.FullSeriesCollection(MLF_ROW).Select

        With Selection.Format.Fill

            .Visible = msoTrue

            .ForeColor.RGB = mlfColor

            .Transparency = 0

            .Solid

        End With

      ActiveChart.FullSeriesCollection(MLF_ROW).Points(1).ApplyDataLabels

        ActiveChart.FullSeriesCollection(MLF_ROW).Points(1).DataLabel.Select

        'Selection.Left = MLF_ROW * 600 / Rows1 'move label by x position increment, about 800 pixels wide for a 9" wide plot

        Debug.Print "xpos = "; Range("LabelXPos").Item(MLF_ROW, 1).Value

        Debug.Print "xpos max = "; Range("LabelXPos").Item(Rows1, 1).Value

        Selection.Left = Range("LabelXPos").Item(MLF_ROW, 1).Value / Range("LabelXPos").Item(Rows1, 1).Value * 600 + 15

        Selection.Top = 65

        ActiveChart.FullSeriesCollection(MLF_ROW).DataLabels.Select

        ActiveChart.FullSeriesCollection(MLF_ROW).HasLeaderLines = False

  Selection.ShowSeriesName = True

        'Selection.ShowLegendKey = -1

        With Selection.Format.TextFrame2.TextRange.Font.Fill

            .Visible = msoTrue

            .ForeColor.RGB = mlfColor

            .Transparency = 0

            .Solid

        End With

        Selection.Format.TextFrame2.TextRange.Font.Size = 10

        Selection.Format.TextFrame2.TextRange.Font.Bold = False

        Selection.Orientation = 30

    Next MLF_ROW

    Range("A1").Select

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

1 answer

Sort by: Most helpful
  1. Anonymous
    2017-08-09T23:33:09+00:00

    I figured it out.  It looks like a default for labels might be to also show value.  However, when I checked the properties of the label, only the show series name was checked.  

    So there is still a bug in that the show value is actually set, but property display check box does not show it being set.

    WORKAROUND...

    Randomly/accidentally/luckily I checked and unchecked the value box and the junk disappeared.

    So I did that with the code:

    Right after this command 

         Selection.ShowSeriesName = True

    I added just one more line of code:

         Selection.ShowValue = False

    It works great now.

    Was this answer helpful?

    0 comments No comments