changing color of listbox

[ain79]

i want to chage the backgroud colour of any row of a listbox.

for example if a list box got 10 values then change the bg color of value no 5 and 8 to green.

 

[PaulBricker]

Sorry, I don't think what you want to do is possible. Access doens't give us much formatting capabilities in Combo/List boxes.

Paul

 

[compucop]

This question is asked alot. I had the same problem several months ago, after being told I had to buy a third party grid control, I almost gave up. There is a way to accomplish this, you need to use the activex listview control 6.0. You can conditionally format this control.If you query in MS VBA forums for listview control, you will see I answered the question already for somebody. If you can't find it post again and I will give it again. It requires VBA to make it work. Later.

 

[compucop]

Might as well post it again, Hope it helps.

You must first put the activex listview 6.0 controller on your form.

On the load event of your form. Put the following code.


dim lv as object
dim col as MsComctLib.ColumnHeader
(dim col1, col2, etc for each column you want in the listbox with the MsComctlib.Columheader extension)

Me.Listview0.fullrowselect= true

' (Listview0 is the name of the control on your form. (will give it full row select to act like a listbox)

set lv = Me.Listview0

'''(Listview0 is name of listview control on your form)

lv.view = 3

''' (report view for the listview controller. Makes it act like a listbox.)

lv.listitems.clear
lv.columnheaders.clear

''(clears both listitems and columnheaders each time program runs, else you will have duplicate date in listbox. )

set col = lv.columnheaders.add(,,"NAME of YOUR COLUMN",listview0.width / 8)

' (DO THIS FOR ALL OF YOUR COLUMNS THAT YOU WANT, THE NAME YOU PUT WILL BE THE NAME DISPLAYED IN THE COLUMN HEADER BOX. THE LISTVIEW0.WIDTH IS THE WIDTH OF THE COLUMN....)

**** I suggest placing the following code in the vba modules section and call the sub everytime you want to requery the listbox. You need to loop a recordset and return the values to the listbox.

''(Call the sub from the modules section)

listloop
end sub

***********************************************************
Sub listloop()
dim lv as object
dim itm as MsComctlib.listitem

dim rs as adodb.recordset
dim cn as adodb.connection

set rs = new adodb.recordset
set cn = currentproject.connection

set lv = forms!NameofForm!Listview0

''''(Name of Form is the form in which the listview controller is on, the control name is the listview controller)

rs.open "SELECT * FROM NAME TABLE",cn,adopenstatic

lv.listitemsclear
lv.columnheaders.clear
''' again clear the listitems and columnheaders, necessary when requerying the listbox.

do until rs.eof

set itm = lv.listitems.add(,,rs!tablename)
itm.subitems(1) = (rs!nexttablename)
itm.subitems(2) = (rs!nexttable)
''' the first statement is for the first column in the listbox, then the subitems are for each additional column, keep adding subitems in increments to incorporate all of your columns.. Make sure you have the columnheaders in the first subroutine declared, else they won't populate. The rs!tablename is the name of the tables you want to put into the listview controller. ..)

*** to conditionally format the items in the listview controller, use the following with an IF statement or whatever you want, they can be static or change as you see fit.

itm.forecolor = vbred

'' (only good for first column in listview controller, will change the color for all other columns use the below statement.)

itm.listsubitems(1).forecolor = vbred
itm.listsubitems(2).forecolor = vbwhatever
rs.movenext
Loop

Give me a yell if you need anything else.