Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

This is a discussion on VBA-automatically moving graph labels above the bar if positive and below if negative? within the Excel Questions forums, part of the Question Forums category; I have a waterfall based on a line graph with up/down bars. ( Excel Waterfall Charts (Bridge Charts) - Peltier ...

  1. #1
    New Member
    Join Date
    Dec 2016
    Posts
    21

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

    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?

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    2,756

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

    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 by Worf; Dec 17th, 2016 at 01:57 PM.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)
    Read Forum Rules here

  3. #3
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    2,756

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

    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)
    Read Forum Rules here

  4. #4
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    2,756

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

    It would look like this:

    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)
    Read Forum Rules here

  5. #5
    New Member
    Join Date
    Dec 2016
    Posts
    21

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

    Quote Originally Posted by Worf View Post
    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?

  6. #6
    New Member
    Join Date
    Dec 2016
    Posts
    21

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

    Please see the link I posted above for a screenshot of my graph and the data that it is looking at

  7. #7
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    2,756

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

    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.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)
    Read Forum Rules here

  8. #8
    New Member
    Join Date
    Dec 2016
    Posts
    21

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

    Quote Originally Posted by Worf View Post
    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!

  9. #9
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    2,756

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

    Got the file! Will work on it as soon as possible, busy week as always...
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)
    Read Forum Rules here

  10. #10
    New Member
    Join Date
    Dec 2016
    Posts
    21

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

    Quote Originally Posted by Worf View Post
    Got the file! Will work on it as soon as possible, busy week as always...
    well if you can make any more progress than I, that would be amazing and much appreciated!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com