VBA-automatically moving graph labels above the bar if positive and below if negative?

hmmmidk220

Board Regular
Joined
Dec 14, 2016
Messages
55
I have a waterfall based on a line graph with up/down bars. (Excel Waterfall Charts (Bridge Charts) - Peltier Tech Blog)

Basically,

The bars on my graph track changes. The only label displayed above/below each bar is a label that says the amount of the change. This is on a hidden secondary axis and is based on a series independent of the actual bars.

I don't know if that makes sense or not lol but is there a VBA solution to make labels align above a bar if the value of the label is positive and below the bar if the value is negative?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
If you are comparing two series with up and down bars, data labels for the second series will be in the correct positions to show the amount of change. The code below does that:Sorry all, problems with the code tags... Root cause is bad Internet connection...Sub UpDown()Dim cht As Chart, s1 As Series, s2 As Series, yv1, yv2, i%Set cht = ActiveChartSet s1 = cht.SeriesCollection(1) ' first seriesSet s2 = cht.SeriesCollection(2) ' second seriess1.HasDataLabels = Falses2.HasDataLabels = TrueWith s2.DataLabels .Font.Bold = 1 .Font.Size = 12 .Position = xlLabelPositionCenterEnd Withyv1 = s1.Valuesyv2 = s2.ValuesFor i = 1 To UBound(yv1) s2.DataLabels(i).Text = yv2(i) - yv1(i) ' amount of change Select Case yv2(i) - yv1(i) Case Is > 0 s2.DataLabels(i).Top = s2.DataLabels(i).Top - 10 ' fine tune position Case Is < 0 s2.DataLabels(i).Top = s2.DataLabels(i).Top + 10 End SelectNextEnd Sub
 
Last edited:

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
A decent posting format:

Code:
Sub UpDown()
Dim cht As Chart, s1 As Series, s2 As Series, yv1, yv2, i%
Set cht = ActiveChart
Set s1 = cht.SeriesCollection(1)                                ' first series
Set s2 = cht.SeriesCollection(2)                                ' second series
s1.HasDataLabels = False
s2.HasDataLabels = True
With s2.DataLabels
    .Font.Bold = 1
    .Font.Size = 12
    .Position = xlLabelPositionCenter
End With
yv1 = s1.Values
yv2 = s2.Values
For i = 1 To UBound(yv1)
    s2.DataLabels(i).Text = yv2(i) - yv1(i)                     ' amount of change
    Select Case yv2(i) - yv1(i)
        Case Is > 0
            s2.DataLabels(i).Top = s2.DataLabels(i).Top - 10    ' fine tune position
        Case Is < 0
            s2.DataLabels(i).Top = s2.DataLabels(i).Top + 10
    End Select
Next
End Sub
 

hmmmidk220

Board Regular
Joined
Dec 14, 2016
Messages
55
It would look like this:


https://postimg.org/image/c0fs9x54p/

Thank you for that Worf! It already gave me some ideas. But the problem I have is that I have four series not two. I followed this advice: Excel Waterfall Charts (Bridge Charts) - Peltier Tech Blog




Another problem is that the fourth series (called Data Label Value) is only there so that data labels can appear. The other series don't track the amount of change.

Does that make sense?
 

hmmmidk220

Board Regular
Joined
Dec 14, 2016
Messages
55
Please see the link I posted above for a screenshot of my graph and the data that it is looking at
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
Can you post a link to your test workbook, so I can use the real chart?
If I create my version, some differences may exist.
 

hmmmidk220

Board Regular
Joined
Dec 14, 2016
Messages
55
Can you post a link to your test workbook, so I can use the real chart?
If I create my version, some differences may exist.
Sure, I'm not sure how to show the file to you, I've uploaded the excel file here:


https://ufile.io/99dcb


But if there is some other way, please let me know. And thanks again for any help!
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
Got the file! Will work on it as soon as possible, busy week as always... :rolleyes:
 

Watch MrExcel Video

Forum statistics

Threads
1,095,549
Messages
5,445,137
Members
405,317
Latest member
gcallaway

This Week's Hot Topics

Top