Removing small value datalabels in stacked bar chart

[cmoffice]

Hello,

I would like to remove small value datalabels in a stacked column barchat.
If you look at the image attached, the small value datalabels tend to clutter the image.

My graph is a MSGraph.Chart.8 inside a Report.
I am working with Access 2010.

Looks like the only possibility to remove the small values is to do that programmaticaly in VBA.

I would like a method that I could call with two parameters : graphname and a threshold value as of which small value datalabels are not displayed.

Can you help on this (My VBA skills are very limited) ?

Thank you,

 

[JHB]

Have you found a solution?

 

[cmoffice]

Hello JHB,
Not yet.
I have experienced some VBA code but that does'nt work.
My problems :
1/ My graphs is now completely blank. Not sure that I must trigger my procedure in the Report Load
2/ The code is working with the datalabels value. But I would like to work directly with the datasheet value. So that I don't have the worry about the datalabels formatting

I appreciate if you can help,
Thank you,


Private Sub Report_Load()

Call RemoveSmallValuesFromChart(100, Me.Graph47.Object)

End Sub
Sub RemoveSmallValuesFromChart(threshold As Double, chartobject As Object)

Dim chart As graph.chart
Dim valuepoint As Double

Set chart = chartobject
For Each series In chart.SeriesCollection

If series.HasDataLabels Then
Dim pointCount As Integer
Dim pointValues As Variant
Dim ser As series

pointCount = series.Points.Count

For pointIndex = 1 To pointCount

valuepoint = CDbl(Split(series.Points(pointIndex).DataLabel.Text, "k")(0))
If valuepoint < threshold Then
series.Points(pointIndex).HasDataLabel = False
End If
Next pointIndex
End If
Next series

End Sub

 

[JHB]

If you move the code to the format event, it works ok by me, see attached picture.
I have set it to remove labels for value lesser as 1900.

 

[cmoffice]

Thanks, it works now with the event format.
How can I change the code so that it works with the real datasheet values instead of the formatted datalabels ?
Thank you,

 

[JHB]

How can I change the code so that it works with the real datasheet values instead of the formatted datalabels ?

Sorry what do you mean, (print screen please )?

 

[cmoffice]

In my code, I am manipulating (with the valuepoint variable) the datalabel itself (which is formatted in k€). So, this is a string that I have to split (with the k) and than convert with Cdbl to retrieve the real value attached to the point. Not really proper. I would like to work directly with the datavalue inside the datasheet and compare that value to the threshold.
Thank you,

 

[JHB]

Sorry - no comprendo.