Adding Data Label To Chart Based On X Values-CodePudding

I have a chart with xvalues Green, Blue, and Red and I want to only add Data Labels to the Red Values. But the colors aren't always in the same order, so I cant use some thing like: Chart.SeriesCollection(n).Points(j).DataLabel.Delete

I want to be able to do something like this: If Chart.SeriesCollection(n).Points(j).DataLabel.XValues = "Red" Then Chart.SeriesCollection(n).Points(j).DataLabel.Delete

But the .XValues command in vba doesnt work like that. Any suggestions? Thanks in advance.

CodePudding user response：

In your description, you say that you want to add data labels to the Red XValues, yet your code deletes them. So I am going to assume that you want to delete the data labels for the Red XValues. Change the chart reference accordingly.

Dim sr As Series
Dim n As Long
Dim j As Long

With Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    For n = 1 To .SeriesCollection.Count
        Set sr = .SeriesCollection(n)
        sr.HasDataLabels = True
        For j = 1 To sr.Points.Count
            If sr.XValues()(j) = "Red" Then
                sr.Points(j).DataLabel.Delete
            End If
        Next j
    Next n
End With

CodePudding user response：

No need for VBA.

Here's my setup. Categories, including maybe "Red" in column A, Values in column B, and chart labels in column C. In cell C2 I'm using a formula like this:

=IF(A2="Red","Label Text Here","")

so there is only text in that column if the X value is "Red".

My chart plots columns A and B of the data range. I added data labels, then formatted the data labels to use Values from Cells, and selected C2:C7 for the cells with labels.

The nice thing is it's dynamic. If the data changes, the labels update without having to add the labels back and rerunning any code.