Moving data labels to variable location on bar chart

[qitjch]

I currently have 13 excel charts that I am having trouble with regarding the data labels. The charts are formatted as combo charts with 2 axis. The Primary axis is a stacked bar chart and the secondary axis is a clustered bar chart. This allows me to display the 'variances' between actual amounts and the forecast amounts on the graphs.

The issue I have is with the Data Labels for the Variance bars (red or green). Currently, there is no option for me to align these data labels on the outside of the bar. Specifically, I would like to align it with the outermost corner. The only way I have managed to accomplish this so far is by manually moving each data label, which is proving to be a bit tedious. Is there any easy way to implement a macro that would determine the coordinates of the corner and position the data label appropriately?

I have attached a 1 page workbook containing both the graphs and fictitious data as reference. So far, I haven't gotten anywhere with the code. My main concern is how to approach determining the positioning of the top corner of each bar in the series and then appropriately moving the data label to said position.

Thank you in advance for the help.

[Andy Pope]

try this.

Sub AlignDataLabel()

    Dim pointIndex As Long
    
    With ActiveChart
        ' positive
        With .SeriesCollection(4)
            For pointIndex = 1 To .Points.Count
                With .Points(pointIndex)
                    If Len(.DataLabel.Text) > 0 Then
                        .DataLabel.Top = .Top - .DataLabel.Height
                        .DataLabel.Left = .Left + (.Width / 2)
                    End If
                End With
            Next
        End With
            
        With .SeriesCollection(5)
            For pointIndex = 1 To .Points.Count
                With .Points(pointIndex)
                    If Len(.DataLabel.Text) > 0 Then
                        .DataLabel.Top = .Top - .DataLabel.Height
                        .DataLabel.Left = .Left
                    End If
                End With
            Next
        End With
            
    End With
End Sub

There is quite a large margin in data label size so if you want the labels nearer to corner you may need to tweak the calculations slightly

[sabael]

Andy Pope
I tested the "AlignDataLabel" code that you suggest in post #2and it works well.
Cheers