listbox color

[tourguy]

I have a form called "frmAdd". Inside this form has a list box called "lbox1"
I have a query called "Age" that captures age.

I am able to get the age into the listbox from the Query, but What i want to do is for the listbox, any age that is below 18, I want it to turn blue, and anything over 18 is turned in red within the listbox.
Please help... thanks!!

 

[kjv1611]

VBA code would probably be best. I would believe you would need to loop through the listitems of the listbox, and use a conditional (if) statement to say something like this:

For Each listitem in List1.ListItems
  If listitem > 18 Then
     listitem.fontcolor = red
  Else If listitem < 18 Then
     listitem.fontcolor = blue
  End If
Next

This is sort of pseudocode, so I'm not garanteeing that it works - not tested. Part of the format would need to be different, but I would think it can be done like this, just making any appropriate format corrections.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[tourguy]

It keeps saying variable not found for listitem when I inserted the code. I also tried changing it from listitem to age.

 

[kjv1611]

Well, it's probably not the correct syntax for the expression. I'll try checking around in the vba helpfile if time permits, and repost the correct values if possible. You could try searching your vba help file for the listbox object and go from there. I believe it is a propery of some sort listed under the listbox - it acts kind of like a recordset...

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Well, I believe I've found it - I believe it is the Selected property, like this:

List1.selected

Try playing around with that property, and see what you get...

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[tourguy]

Thanks... I'll check it now

 

[RoyVidar]

I think you'll have to look further than native listbox controls to achieve conditional formatting. Perhaps a datasheet subform and conditional formatting? Perhaps some ActiveX/third party thingies have something? Perhaps something can be found at Stephen Lebans? These looks promising ListBoxEnhanced, FieldList, but I haven't tried them.

Roy-Vidar

 

[tourguy]

This one gave me an error saying "argument not option". For List1.Selected, I tried using List1.ItemSelected and List1.ItemData, but still gave the same message.

Private Sub Form_Current()
Dim listitem As Variant
For Each listitem In List1.Selected
If listitem > 18 Then
listitem.fontcolor = "8421440"
Else
If listitem < 18 Then
listitem.fontcolor = "255"
End If
End If
Next
End Sub

 

[assets]

There is 2 FAQ's on changing colour of thext or backgroud of fields. It was sesigned for yes/no but works with > or <.

 

[kjv1611]

For the loop part, it'd be best, probably to loop through the selected items like this, as well: from looking at some code someone else here at tek-tips helped me with:

Dim X as Integer
For X = 0 To List1.Listcount - 1
    List1.Selected(X) = True
    If List1 >18 Then
        List1.FontColor = vbRed
    Else If List1 < 18 Then
        List1.FontColor = vbBlue
    End If
Next X

I went ahead and added a longshot idea. I was thinking, "I wonder if you change the List1 fontcolor as you have each item selected, if that would just change the font color for that one item. I figured it was worth a shot.
If that doesn't work, you may have to go with What RoyVidar mentioned, or maybe check out the 2 FAQ's that [blue]assets[/blue] mentioned. To find the FAQ's, do an advanced search, and choose FAQ's to search from, and include as much text as you can from your problem, and you may come up with something there.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV