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