Format of Listbox Data

[illini]

I'm not sure if this is possible or not.

I have a Listbox which is populated via the Row Source (SQL). I would like for the data in certain columns to be alligned center. Can this be done? Currently, all data is defaulted to allign left.

Also, is it possible to have every other row in a listbox to be shaded?

Thanks in advance. -illini

 

[kebbers]

Hi
I don't know about the graying but to set the alignment go into the property sheet and the last option should be text align, you can set it there.

 

[compucop]

Hi,
To get the functionality you require, it is probably best if you use the Microsoft listview 6.0 Active X control. You can center, and conditionally format each row with colors. It requires vba to do, but is not hard.

If this is what your looking for let me know. I will help you out. Later.

 

[illini]

compucop,

The Microsoft listview 6.0 Active X control sounds like what I'm looking for. Vba shouldn't be a problem.

-illini

 

[compucop]

Hope I explain this well.

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

''' so on and so on. This has the ability to change only one row at a time and it can be conditionally formatted. Hope this works well, give me a yell if you need anything else.

Regards.

 

[compucop]

Oops sorry I forgot the following statements to be added at the end. Underneath itm.listsubitems(2).forecolor = whatever

rs.movenext
Loop

Good Luck...