Formating Bars based on the value of a bar

[billmahon]

I have created a bar chart in an access report. I want the bars to change
color based on the value of the specific bar. For example, if the value of a
bar is less than 1, it is red, if it is between 1 and 5, it is yellow, if it
is greater than 5 it is green.

How can I do this?

 

[Rob Parker]

Seems like an easy problem, but I've just spent a couple of hours playing
with this and I can't make it work. The problem is that the Value property
of the points in the SeriesCollection is not exposed in the VBA interface.
It certainly exists (eg. with the chart in design mode, if you hover the
mouse over a specific bar you see "Series SeriesName Point n Value x.xxx"),
but there does not seem to be any way to access it programmatically.

Rob

 

[Rob Parker]

But I won't be beaten easily ;-)

Here's a work-around.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim rst As DAO.Recordset
Dim pnt As Object
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset(Me.Controls("ChartName").RowSource)
With rst
.MoveFirst
For i = 1 To
Me.Controls("ChartName").SeriesCollection("FieldName").Points().Count
Set pnt =
Me.Controls("ChartName").SeriesCollection("FieldName").Points(i)
Select Case !FieldName
Case Is < 1
pnt.Interior.Color = vbRed
pnt.Border.Color = vbRed
Case 1 To 5
pnt.Interior.Color = vbYellow
pnt.Border.Color = vbYellow
Case Is > 5
pnt.Interior.Color = vbGreen
pnt.Border.Color = vbGreen
Case Else
End Select
.MoveNext
Next i
End With
Set rst = Nothing
Set db = Nothing
End Sub

What this is doing is opening a recordset, using the same source as the
RowSource of the chart object. Then we simply loop through the number of
points in the chart's SeriesCollection for the required field, get the value
from the recordset which we've opened, and set the interior and border color
for the current point depending on the value in the recordset (which will be
the same as the value of the point in the series).

Simple ...

HTH,

Rob

 

[billmahon]

This worked like a charm. Thank you so much for your efforts.

One last thing, I have data labels for each bar (the value of the bar), how
do I get them to change to the appropriate color (same as the bar).

 

[Rob Parker]

Like this (you'll need similar in each Case construction):
...
Case Is < 1
pnt.Interior.Color = vbRed
pnt.Border.Color = vbRed
Me.Controls("ChartName").SeriesCollection("FieldName").DataLabels(i).Font.Color
= vbRed
...

Note: yellow text (on a white background) is not easy to see

Again, HTH,

Rob

 

[billmahon]

Awesome. Thanks again, I greatly appreciate the help.

 

[hughess7]

Hi, can you help please? This is also what I want to do but I think my
rowsource is maybe different and I just want clarification on this and some
further explanation please...

I have two rows of data in the datasource, one is based on dealer values and
the 2nd is national values (always 100). The National values are displayed as
a trend line, the dealer values in bar columns (is this the points?). I want
them red if over 100 and blue if under 100. Trying to use this example I get
them all in the same colour, dependant on the values but I want them to
colour depending on each individual columns value?

Thanks in advance for any help.
Sue