• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

Trade Show Database New.. Question on user of row colors with a List Box in Access 2007

Hi Everyone,

I have redesigned a new database now for the Tradeshow which will use a List Box.  I have a row color issue using the List Box in Access 2007.  

I am using a sub routine called Product_click()

I have this VBA Code:   If Product.Column(1) = "VAP" Then
        If Product.Column(4) = 0 Then
            Product.BackColor = HexColor("#ED1C24")
        ElseIf Product.Column(4) > 0 And Product.Column(4) < 12 Then
            Product.BackColor = HexColor("#FFF200")
            Product.BackColor = HexColor("#FFFFFF")
        End If
        Product.BackColor = HexColor("#FFFFFF")
    End If
End Sub

I have attached my database for review.  The database is working like I want it to, but when the Product.Column(4) = "0"  I want the red color for the row to appear and not the entire List box be red.  I want also the same thing to happen for Product.Column(4)  > 0 and < 12 be "yellow'.  I just want the row to turn "yellow" and not the entire list box.

My question, is there a way to just color the row red when the Product.Column(4) = 0 and
the Product.Column(4) > 0 and < 12 be yellow.

What VBA coding would do this?  I just want the row to change and not the entire List Box?

2 Solutions
Can't be done with a listbox.

To get this effect you would need to use a continuuous subform that lists the products and use Conditional Formatting on the controls on that subform.
Jeffrey CoachmanMIS LiasonCommented:
Agree with Pete.

Unfortunately if you really need a Listbox (becuase you need multiple/non-contiguous selections) then you are out of luck with colors.

Is a using Color a hard requirement? (suppose a user is colorblind?)
(Because again, ...it is just not possible to easily get colors in a listbox...)

Another option would be to use Text as a visible indicator
The Source for the listbox might be something like this:
    SELECT Field1, Field2, IIF(Field1=0,"ZERO","NOT ZERO") AS Result
    FROM YourTable
..obviously you would have to use a nested IIF if you had multiple conditions...
But I am sure you get the idea...

But again, if you just need to "see" the list, then use a continuous form as Pete suggested.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now