UserForm(Excel/Project/MSForms) - Color/Font List Box

[BrianZ]

The problem I'm having is that when I create a user form and try to add my list to it....I would like to be able to have two different colors witing one list box. Is there a way to do this? The idea is if a condition is negative the particular row would be red and if the condition is positive then i want the item to be green. It seems like no matter what they all come out the same color!!

Thank you for your time and help!

Brian

[Norie]

Brian

Short answer - No.

Listbox items can't have different colours.

[BrianZ]

There's no way to use some Array to reference the item in the list and use .FontColor based on a Select Case or If Condition??? It just seems like they should add this functionality don't you think??? The Font Property doesn't even show a color........shows everything else.....

So for instance if you were to say

ListBox.ListBox1(i) and reference the line when you load it in and make the text going into the box a certain color?? Ughhhhhh

Help

[johnske]

Suggestions:

You could use two listboxes - one showing negative, the other positive values, or

You could have a button near a (single) list box to flick between the negative and positive values, and load the values accordingly

[Charlize]

Maybe you could take a look at the listview control. This control can change the forecolor of a listviewitem based on a certain condition.

[BrianZ]

Charlize;

Great idea....I have no idea how to use that Contol with VBA within my form. Can you provide some sample code of how to do this? I've downloaded and installed the control, but stumped on how to make it control the forecolor on a listitem.

Thanks,

Brian

[Charlize]

Take a look at this kb article : http://vbaexpress.com/kb/getarticle.php?kb_id=916 to get you started.

[BrianZ]

Charlize;

This is excellent...I'm just about there.......I'm using Microsoft Project VBA instead though.....and I'm having difficulty a bit with the syntax on the form itself. I've gotten everything else done....however......I'm not sure how to write the item/sub item part. Let's say I have 5 columns and 4 rows. If the value in Column number 4 for row 1 is a negative number then I would like the entire ForeColor of that row to be red. If it's a positive value, then I'd like it to be Green. The process would repeat for each row until completion. Can you maybe provide me with the corresponding syntax to equate ProjectVBA from the Excel VBA.....you reference the worksheet....and I'm using somewhere I reference the ActiveProject.....but not sure how the AddItem and the Array values and subitems come into play. Let's just say Column 1 is ID, Column 2 is Task Name, Column3 is StartDate and Column4 is Slack. So if Column4's first character is a "-" Then Forecolor = Red (somehow?!) and if not's then the row should be green!

Thanks for the help

Best Regards,

Brian

[BrianZ]

Charlize;

Figured it out....tricky stuff to convert to ProjectVBA for a newbie but is there a way to click on each of the column headings and sort by them in this listview?



Thanks,

Brian

[Charlize]

This is an enhancement on the link I gave you. It will sort the columnheader named Client :[vba]Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
'If clicked on the columnheading named Client -> do the sorting
If ColumnHeader.Text = "Client" Then
Me.ListView1.Sorted = True
Me.ListView1.SortKey = 0
If Me.ListView1.SortOrder = lvwDescending Then
Me.ListView1.SortOrder = lvwAscending
Else
Me.ListView1.SortOrder = lvwDescending
End If
Else
Me.ListView1.Sorted = False
End If
End Sub[/vba]ps. Sorry I couldn't help with the converting to Project (but I don't know J *** S *** about Project).

[Norie]

Brian

The only difference between Excel VBA and Project VBA should only be the object model and I don't see how that would affect your use of a listview control on a userform.

[Charlize]

This is an enhancement on the link I gave you. It will sort the columnheader named Client :[vba]Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
'If clicked on the columnheading named Client -> do the sorting
If ColumnHeader.Text = "Client" Then
Me.ListView1.Sorted = True
Me.ListView1.SortKey = 0
If Me.ListView1.SortOrder = lvwDescending Then
Me.ListView1.SortOrder = lvwAscending
Else
Me.ListView1.SortOrder = lvwDescending
End If
Else
Me.ListView1.Sorted = False
End If
End Sub[/vba]ps. Sorry I couldn't help with the converting to Project (but I don't know J *** S *** about Project).

Was it usefull ?