CODEWITHSUNDEEP
excelexcel-2016excel-charts
Terry W
Terry W

Reputation: 3257

How to let Excel Chart data label automatically adjust its position based on certain criteria?

I have a line chart showing month-on-month sales movement (either Up or Down) for 12 months. I have two sets of data and I have set a control to select one set of data at a time for reporting purpose.

The first problem is that after I created the line chart, all data labels are defaulted to be shown above the line. However I prefer to show the 'Down' label under the line. At the moment I have to manually drag the ones that are 'Down' beneath the line.

The second problem is that after I changed the data set, say from Set1 to Set2, the data label positions are distorted and I have to manually adjust them one by one if needed.

I would like to know

if there is an option to let the data label automatically adjust its position (such as above or below the line) based on certain criteria (such as TRUE vs FALSE, or positive vs negative)?

sample

Thanks in advance!

Upvotes: 2

Views: 1984

Answers (1)

BigBen
BigBen

Reputation: 50007

It should be pretty easy to loop through the data labels, checking whether they contain "Down" or "Up" and adjusting their position accordingly.

Bonus points to you if you figure out how to trigger this when the dropdown changes (hint: you should be able to use the Worksheet.Change event).

Sub UpdateLabelPosition()
    Dim myChart As ChartObject
    Set myChart = Sheet1.ChartObjects("Chart 1") ' change sheet and chart name as needed

    With myChart.Chart.SeriesCollection(1)
        If .HasDataLabels Then

            Dim myLabel As DataLabel
            For Each myLabel In .DataLabels
                If InStr(myLabel.Text, "Down") > 0 Then
                    myLabel.Position = xlLabelPositionBelow
                ElseIf InStr(myLabel.Text, "Up") > 0 Then
                    myLabel.Position = xlLabelPositionAbove
                End If
            Next

        End If
    End With
End Sub

EDIT:

Taking the approach that the corresponding value is positive or negative, perhaps something like this:

Sub UpdateLabelPosition()
    Dim myChart As ChartObject
    Set myChart = Sheet1.ChartObjects("Chart 1") ' change sheet and chart name as needed

    With myChart.Chart.SeriesCollection(1)
        Dim myValues
        myValues = .Values

        Dim i As Long
        For i = LBound(myValues) To UBound(myValues)
            If .Points(i).HasDataLabel Then
                If myValues(i) < 0 Then
                    .Points(i).DataLabel.Position = xlLabelPositionBelow
                Else
                    .Points(i).DataLabel.Position = xlLabelPositionAbove
                End If
            End If
        Next
    End With
End Sub

Upvotes: 1

Related Questions