Display data labels on stacked bar pivot chart ONLY for values greater than 0

**YippeeKiYay** · 01-21-2009, 03:44 AM

Hi, guys,

Would you please help?

I have a stacked bar pivot chart, which only displays the values greater than 0, but I don't want any labels from values equal to 0 showing up and making the chart basically unreadable. I just need the labels from values greater than 0, but getting them automatically with VBA code (macro).

The way I´m doing this is referencing the seriescollection "points" which need to have labels applied. In another worksheet ("referencia") I basically copy the information from the pivot table from all 31 data fiels. Then, range D3:D33 contains the data field number if the value is greater than 0.

Here's my code:

Sub datalabels1()

Dim c
Dim s As Variant

For Each c In Worksheets("referencia").Range("D3:D33").Cells

' If the value in the cell is greater than 0, then variable s gets such value

        If c.Value > 0 Then s = c.Value
    
' Open the worksheet and chart
    Worksheets("Paretos QCPC Dia&Sem").Activate
    ActiveSheet.ChartObjects("Chart 14").Activate
    
' Apply data label to value, since it was greater then 0
    ActiveChart.SeriesCollection(s).ApplyDataLabels AutoText:=True, LegendKey:= _
        False, ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=True, _
        ShowPercentage:=False, ShowBubbleSize:=False, Separator:=" "

Next

End Sub

When I run this, I get runtime error '1004': Method 'SeriesCollection' of object '_Chart' failed. I think it has to do with my using the s variable on:

ActiveChart.SeriesCollection(s).ApplyDataLabels

, since when I change it to a number, it works fine.

I hope I made sense, but it's late here and I'm desperate.

Thank you,
José

**Andy Pope** · 01-21-2009, 09:43 AM

How about using a custom number format for the data labels?

General;;

**YippeeKiYay** · 01-21-2009, 10:14 AM

What do you mean?

Thank you,
José

**Andy Pope** · 01-22-2009, 06:43 AM

I missed in your code that your data label is actually displaying both Value and Series Name, in which case the custom number format will not work.

What is the variable s referring to in your code?

**YippeeKiYay** · 01-26-2009, 07:58 PM

Hi,

The variable "s" is for the series point I need the label for.

See, I have 31 columns on the pivot table, each of which have different values. All of these columns are in the pivot chart, but most have values equal to 0, so they're not displayed. I want to be able to automatically display labels for only all column values greater than 0.

What I've done is reference the values in the pivot table on other cells, and if the value is greater than 0, the code has "s" equal to it, so that the "activechart.seriescollection(s).applydatalabels" can work.

Thank you,
José

**Andy Pope** · 01-27-2009, 11:22 AM

Does the range D3:D33 contain zero or the Index number of the series?
If not, if the range contains actual data values then I guess it will fail when the value exceeds the number of series.
Instead of reading the value of s from the range just use a counter

Also the labels will be applied to the whole series, which may contain points with a zero value.

Sub datalabels1()

Dim c
Dim s As Variant
dim lngSeries as long

For Each c In Worksheets("referencia").Range("D3:D33").Cells
    
    lngSeries = lngSeries + 1

' If the value in the cell is greater than 0, then variable s gets such value

        If c.Value > 0 Then     
            ' Open the worksheet and chart
            with Worksheets("Paretos QCPC Dia&Sem").ChartObjects("Chart 14").chart
    
' Apply data label to value, since it was greater then 0
                .SeriesCollection(lngSeries).ApplyDataLabels AutoText:=True, LegendKey:= _
        False, ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=True, _
        ShowPercentage:=False, ShowBubbleSize:=False, Separator:=" "

           End with
       End If
Next

End Sub

If still not working can you post small example file.