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

Posted on 2012-08-31
Last Modified: 2012-08-31
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?

Question by:jjc9809
    LVL 77

    Assisted Solution

    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.
    LVL 74

    Accepted Solution

    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, 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.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now