Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    column charts labels

    greetings,

    I have more than a hundred column charts in one sheet and I am looking for a way to have data labels outside the end for all the charts in one click.
    TIA
    dubdub

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Try this code:

    In a standard module
    Code:
    Public Sub ApplyLabels()
    Application.ScreenUpdating = False
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
        cht.Select
        ActiveChart.SeriesCollection(1).ApplyDataLabels
    Next cht
    Application.ScreenUpdating = True
    End Sub
    HTH,
    Maud

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    To remove the labels, use this code

    Code:
    Public Sub RemoveLabels()
    Application.ScreenUpdating = False
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
        cht.Select
        ActiveChart.SeriesCollection(1).DataLabels.ShowValue = False
    Next cht
    Application.ScreenUpdating = True
    End Sub

  4. #4
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thank you Maud, I ran the code for two charts, each one has three column and the code apply the labels in the first column only.
    TIA
    dubdub

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi dubdub

    ..why not add another loop to process each SeriesCollection??
    ..or, if there are always going to be 3 columns in the charts, use..
    Code:
    Public Sub ApplyLabels()
    Application.ScreenUpdating = False
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
        cht.Select
    for i=1 to 3
        ActiveChart.SeriesCollection(i).ApplyDataLabels
    next i
    Next cht
    Application.ScreenUpdating = True
    End Sub
    zeddy

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Zeddy,

    For charts that have the same or varying number of series (columns), this may be a more versatile method

    Code:
    Public Sub ApplyLabels()
    Application.ScreenUpdating = False
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
        cht.Select
        For I = 1 To cht.Chart.SeriesCollection.Count
            ActiveChart.SeriesCollection(I).ApplyDataLabels
        Next I
    Next cht
    Application.ScreenUpdating = True
    End Sub
    
    
    Public Sub RemoveLabels()
    Application.ScreenUpdating = False
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
        cht.Select
        For I = 1 To cht.Chart.SeriesCollection.Count
            ActiveChart.SeriesCollection(I).DataLabels.ShowValue = False
        Next I
    Next cht
    Application.ScreenUpdating = True
    End Sub
    DubDub,

    If you find that the labels for the 3 series run into each other, you can add the following code to rotate the data labels 90 degrees and change the font size

    Code:
    Public Sub ApplyLabels()
    Application.ScreenUpdating = False
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
        cht.Select
        For I = 1 To cht.Chart.SeriesCollection.Count
            ActiveChart.SeriesCollection(I).ApplyDataLabels 'CREATE DATA LABELS
            With ActiveChart.SeriesCollection(I).DataLabels
                .Orientation = 90 'ROTATE DATA LABELS
                .Format.TextFrame2.TextRange.Font.Size = 6 'CHANGE FONT SIZE
            End With
        Next I
    Next cht
    Application.ScreenUpdating = True
    End Sub
    dub1.png
    Last edited by Maudibe; 2016-08-08 at 19:49.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Maud

    Your versatile method is indeed versatile.

    Hi dubdub..why not add another loop to process each SeriesCollection??
    I was trying to get dubdub to understand your code, and then perhaps learn from it, and then perhaps make the leap and try for themselves. So I only gave the simple suggestion to show a loop within a loop.

    But another way of learning to write vba code is to look at as many code examples as possible.
    Your code posts are a great way for learning how to do stuff.

    I like it very much when I see posts that say "many thanks for your code - I was able to learn from it and adapt it to deal directly with my situation - I'm getting better at vba" etc etc etc.
    This is much better than getting Lounge helpers to do all the work.
    (and those posters know who they are!)

    zeddy
    Last edited by zeddy; 2016-08-09 at 04:56.

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Indeed, looping through the series set as you demonstrated is certainly the most appropriate technique to address the properties of each series.

    Maud

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Great thanks to you Maud for the help and for all the help I get from you and form all members.
    TIA
    dubdub

  10. #10
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Zeddy, thanks for your help, i defiantly learn from any proposed answer(s) to resolve any future needs.
    TIA
    dubdub

Tags for this Thread

Posting Permissions

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