Hiding data labels for some, not all values in a series

[foobar404]

Here's a good challenge for you. I can't figure it out, and I believe it's a limitation of Excel.

I have a bar graph with several data series. I know how to show the data labels for every data point in a given series. But I'm looking to show the data label for only some data points in a given series -- i.e. non-zero valued data points. The reason is, when a data point is zero, the displayed data label bunches up with others, and becomes unreadable clutter.

Is there a way? Can it be done? I tried to edit the chart's data label properties one-by-one in VBA, but this didn't work.

Cheers,
Aaron

 

[Glove_Man]

Is this a one-off or a conitnuing problem?

I wrote the following to handle a very similar problem:

Sub Create_Data_Labels_Daily()

Worksheets("Daily").ChartObjects("Chart 28").Activate

    ActiveChart.SeriesCollection(1).ApplyDataLabels LegendKey:=False, _
        ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=False, _
        ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(2).ApplyDataLabels LegendKey:=False, _
        ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=False, _
        ShowPercentage:=False, ShowBubbleSize:=False

For i = 1 To 20
    
    P_TW = [PV3].Cells(i).Value
    pname = [pv2].Cells(i).Value2
    pval = [pv1].Cells(i).Value2
    
    If IsError(pname) = False Then
        
        If P_TW = "T" Then
            series_num = 2
            ActiveChart.SeriesCollection(series_num).Points(pval).HasDataLabel = True
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Text = pname

            
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Position = xlLabelPositionOutsideEnd
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Orientation = xlDownward
        
        Else
            series_num = 1
            ActiveChart.SeriesCollection(series_num).Points(pval).HasDataLabel = True
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Text = pname


            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Position = xlLabelPositionOutsideEnd
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Orientation = xlUpward

        
        
        End If

    End If

Next i

                ActiveChart.SeriesCollection(1).DataLabels.Font.FontStyle = "Regular"
                ActiveChart.SeriesCollection(1).DataLabels.Font.Size = 12
                ActiveChart.SeriesCollection(2).DataLabels.Font.FontStyle = "Regular"
                ActiveChart.SeriesCollection(2).DataLabels.Font.Size = 12
Range("a1").Activate

End Sub

 

[foobar404]

Continuing problem. Yes -- your solution was perfect; I adapted it to solve my problem. Many thanks!

Is this a one-off or a conitnuing problem?

I wrote the following to handle a very similar problem:

Sub Create_Data_Labels_Daily()

Worksheets("Daily").ChartObjects("Chart 28").Activate

    ActiveChart.SeriesCollection(1).ApplyDataLabels LegendKey:=False, _
        ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=False, _
        ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(2).ApplyDataLabels LegendKey:=False, _
        ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=False, _
        ShowPercentage:=False, ShowBubbleSize:=False

For i = 1 To 20
    
    P_TW = [PV3].Cells(i).Value
    pname = [pv2].Cells(i).Value2
    pval = [pv1].Cells(i).Value2
    
    If IsError(pname) = False Then
        
        If P_TW = "T" Then
            series_num = 2
            ActiveChart.SeriesCollection(series_num).Points(pval).HasDataLabel = True
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Text = pname

            
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Position = xlLabelPositionOutsideEnd
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Orientation = xlDownward
        
        Else
            series_num = 1
            ActiveChart.SeriesCollection(series_num).Points(pval).HasDataLabel = True
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Text = pname


            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Position = xlLabelPositionOutsideEnd
            ActiveChart.SeriesCollection(series_num).Points(pval).DataLabel.Orientation = xlUpward

        
        
        End If

    End If

Next i

                ActiveChart.SeriesCollection(1).DataLabels.Font.FontStyle = "Regular"
                ActiveChart.SeriesCollection(1).DataLabels.Font.Size = 12
                ActiveChart.SeriesCollection(2).DataLabels.Font.FontStyle = "Regular"
                ActiveChart.SeriesCollection(2).DataLabels.Font.Size = 12
Range("a1").Activate

End Sub

 

[Glove_Man]

You're Welcome!!

 

[Jon Peltier]

Gee, all that code.

Apply a custom number format that suppresses zeros, something like

0;0;;

or

0.00;0.00;;

For more hints about number formats:

http://peltiertech.com/Excel/NumberFormats.html

 

[Sam80]

Hello mates,
I am totally new in VB in Excel. But I have a general question. I have seen Glove_Man has written very nice code.
I was trying to learn something from his code. I have found in the debug mode I do not have any method in ActiveChart which is called SeriesCollection ;however, when I call ActiveChart.SeriesCollection(series_num) I will see the properties on this object. My main question is that how Glove_Man knew that ActiveChart has SeriesCollection object.

Thank you in advanced!