PDA

View Full Version : Moving data labels to variable location on bar chart



qitjch
03-16-2016, 11:48 AM
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
03-17-2016, 09:11 AM
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
11-02-2018, 04:13 PM
Andy Pope
I tested the "AlignDataLabel" code that you suggest in post #2and it works well.
Cheers