Charts : Update data labels to certain number of decimal pla

  • Good Afternoon All!


    Im stuck again!


    I have a bar chart with its values showing.....I would like to have some VBA code which updates the data label depending on the number of decimal places the value is. Ie the first label may be 99.09 and the second 99.10 and the third 98.00
    The code should adjust the labels to be 99.09 and 99.1 and 98


    Currently My code changes the format to two decimal places:
    ActiveSheet.ChartObjects("Chart 14").Activate
    ActiveChart.SeriesCollection(3).DataLabels.Select
    Selection.NumberFormat = "0.00%"


    I guess I need an IF...then ???


    Does anyone have anything similar to this???


    Thnks


    Dani

  • Hi Dani


    I think this could be solved without any code. The format of the labels is the same as the data the chart is based on, so if you format the cells which the bar chart is based on to "General" the data labels will get the same format.


    I hope this helps.

  • Hiya! Thanks but.....
    Unfortunately thats not possible!! These are percentages and I would have to change the formulas to *100 to do that...I have 48 charts!!!!! With zillions of percentages......


    Any other suggestions???

  • Hi daniB,


    Here is a macro that will alter the NumberFormat.



    If you can live with a datalabel for the value 98.0% of 98.% then you can simply use the numberformat 0.##% on the cells and as Fwind points out the formatting will apply without code.


    Cheers
    Andy

  • Hiya.


    That was just what I was looking for, however a very strange thig is happening....
    The code works perfectly the first time....then maybe the second time, but then randomly appears runtime error message:


    "Unable to get the count property of the Datalabels class"


    I have checked all my datalabels to be the same.....I dont know why this happenes?


    Ive tested it out on 10 different charts....most of them work a couple dont...I dont understand why this happens? Any ideas?


    Thanks
    Dani

  • Hi daniB,


    The problem is caused by running the code on a chart that does not have data labels.
    The revised code checks that the series has data labels.


    Sub FormatLabels()
    Dim intLabel As Integer
    Dim intSeries As Integer
    Dim intPlaces As Integer
    With ActiveChart
    For intSeries = 1 To .SeriesCollection.Count
    If .SeriesCollection(intSeries).HasDataLabels Then
    For intLabel = 1 To .SeriesCollection(intSeries).DataLabels.Count
    With .SeriesCollection(intSeries).DataLabels(intLabel)
    .NumberFormat = "0.00%"
    intPlaces = 2
    Do While intPlaces >= 0
    If Mid(.Text, Len(.Text) - 1, 1) = "0" Then
    intPlaces = intPlaces - 1
    Else
    Exit Do
    End If
    If intPlaces = 0 Then
    .NumberFormat = "0%"
    Exit Do
    Else
    .NumberFormat = "0." & String(intPlaces, "0") & "%"
    End If
    Loop
    End With
    Next
    End If
    Next
    End With
    End Sub



    Cheers
    Andy

  • Re: Charts : Update data labels to certain number of decimal pla


    Hi All,


    I have similar problem but I am new to VBA so cannot modify above code as per the my need :(


    I am Working with following kind of Data structure.


    Category A
    [TABLE="width: 128"]

    [tr]


    [td]

    Product

    [/td]


    [td]

    Sales

    [/td]


    [/tr]


    [tr]


    [td]

    P1

    [/td]


    [td]

    12876

    [/td]


    [/tr]


    [tr]


    [td]

    P2

    [/td]


    [td]

    10987

    [/td]


    [/tr]


    [tr]


    [td]

    P4

    [/td]


    [td]

    876

    [/td]


    [/tr]


    [tr]


    [td]

    P3

    [/td]


    [td]

    567

    [/td]


    [/tr]


    [/TABLE]


    Category B
    [TABLE="width: 128"]

    [tr]


    [td]

    Product

    [/td]


    [td]

    Sales

    [/td]


    [/tr]


    [tr]


    [td]

    P1

    [/td]


    [td]

    78965

    [/td]


    [/tr]


    [tr]


    [td]

    P2

    [/td]


    [td]

    48965

    [/td]


    [/tr]


    [tr]


    [td]

    P3

    [/td]


    [td]

    25874

    [/td]


    [/tr]


    [tr]


    [td]

    P4

    [/td]


    [td]

    120

    [/td]


    [/tr]


    [/TABLE]



    I have one Form Combo Box containing list 'Category A, Category B' and I linked this Combo Box to above two data ranges using Indirect Function.


    for example my Output range is C8:D11 (which is Pie Chart range) and if I select Category A then Category A data will be displayed and if I select Category B then Category B data will be displayed in C8:D11 and so chart will change dynamically.


    I want Data Label format "0%" if value>1 for that Data Label and 0.0% format if Value<1, 0.00% if value<0.1 etc.


    For Category A Data Labels are 51%, 43%, 3%, 2% .
    For Category B, Data Labels are 51%, 32%, 17%, 0% (=0.07 which I want to be dispalyed as 0.1 %).


    Problem is that I cannot find a way to have dynamic number format for Data Labels when A and B Categories are selected from Combo Box.


    Is it possible to wright VBA code (that i will assign to Combo Box) which checks each data label for above kind of conditions like set "0%" format for data label if Value>1, "0.0%" format if 0.1<value<1 and "0.00%" format if value<0.1 ?


    Thanks.

  • Re: Charts : Update data labels to certain number of decimal pla


    Welcome to Ozgrid, ibdave19.


    This thread is over 10 years old. While it may be relevant to your issue, the policy on the board is you do not post questions in threads started by other members, regardless how old they are.


    Please start your own thread. Give it an accurate and concise title that summarises your problem and explain your issue fully.


    If you think this, or any other thread, can help clarify your problem you can include a link by copying the URL from the address bar of your browser and pasting into your message.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!