Formating Bars based on the value of a bar

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?

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

"billmahon" wrote in message
...
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?

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").RowSourc e)
With rst
.MoveFirst
For i = 1 To
Me.Controls("ChartName").SeriesCollection("FieldNa me").Points().Count
Set pnt =
Me.Controls("ChartName").SeriesCollection("FieldNa me").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

"Rob Parker" wrote in message
...
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

"billmahon" wrote in message
...
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?

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" wrote:

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").RowSourc e)
With rst
.MoveFirst
For i = 1 To
Me.Controls("ChartName").SeriesCollection("FieldNa me").Points().Count
Set pnt =
Me.Controls("ChartName").SeriesCollection("FieldNa me").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

"Rob Parker" wrote in message
...
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

"billmahon" wrote in message
...
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?

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("FieldNa me").DataLabels(i).Font.Color
= vbRed
...

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

Again, HTH,

Rob


"billmahon" wrote in message
...
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" wrote:

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").RowSourc e)
With rst
.MoveFirst
For i = 1 To
Me.Controls("ChartName").SeriesCollection("FieldNa me").Points().Count
Set pnt =
Me.Controls("ChartName").SeriesCollection("FieldNa me").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

"Rob Parker" wrote in
message
...
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

"billmahon" wrote in message
...
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?

Awesome. Thanks again, I greatly appreciate the help.

"Rob Parker" wrote:

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("FieldNa me").DataLabels(i).Font.Color
= vbRed
...

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

Again, HTH,

Rob


"billmahon" wrote in message
...
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" wrote:

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").RowSourc e)
With rst
.MoveFirst
For i = 1 To
Me.Controls("ChartName").SeriesCollection("FieldNa me").Points().Count
Set pnt =
Me.Controls("ChartName").SeriesCollection("FieldNa me").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

"Rob Parker" wrote in
message
...
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

"billmahon" wrote in message
...
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?

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


"Rob Parker" wrote:

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").RowSourc e)
With rst
.MoveFirst
For i = 1 To
Me.Controls("ChartName").SeriesCollection("FieldNa me").Points().Count
Set pnt =
Me.Controls("ChartName").SeriesCollection("FieldNa me").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

"Rob Parker" wrote in message
...
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

"billmahon" wrote in message
...
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?