PDA

View Full Version : Change the color of text in a listbox based upon a certain criteria



xacis
08-12-2006, 09:39 PM
As title, how can I change the color of text in a listbox based upon a certain criteria? For example, there is four columns in the listbox and the second column contains the numbers. If the number of certain row > 1, I want the text of that row change to red color. How can I do that?

Thanks for your attention.


xacis

mdmackillop
08-13-2006, 03:32 AM
Hi Xacis,
Welcome to VBAX.
You can change text colour using the listbox ForeColor property, but you can't have more than one colour.
Regards
MD

xacis
08-13-2006, 05:02 AM
So, is this a limitation in VBA? If yes, any other method could solve this problem?
Thx a lot!!!

mdmackillop
08-14-2006, 12:21 AM
You could filter your data to show only "highlighted" rows. I can't think of any way to show what you're looking for and give you listbox funcionality
Regards
MD

xacis
08-27-2006, 12:06 AM
Thx a lot.

Norie
08-27-2006, 07:56 AM
It's not possible to change the colour of an individual item in a listbox.

If you change the BackColor it changes for all items.

Cyberdude
08-27-2006, 01:36 PM
Malcolm and Norie sure spell "color" funny.

Norie
08-27-2006, 03:52 PM
Malcolm and Norie sure spell "color" funny.
Ah, but correctly.:bug:

Charlize
09-08-2006, 04:32 AM
As title, how can I change the color of text in a listbox based upon a certain criteria? For example, there is four columns in the listbox and the second column contains the numbers. If the number of certain row > 1, I want the text of that row change to red color. How can I do that?

Thanks for your attention.


xacis

Hi Xacis,

I have found a way to accomplish this task. Instead of using a listbox I've used listview. Add this control by right clicking on the workpalette and choose 'Microsoft Listview Control 6.0 (SP4)'

Set following code in the userform1.initialize


Option Explicit 'this one above the userform1.initialize
Private Sub UserForm_Initialize()
Dim startrow As Integer 'beginning of data
Dim endrow As Integer 'end of data
Dim pos As Integer 'actual row
Dim lv_item As Integer 'no of the listview item
Dim counting As Integer 'loop for processing all items
startrow = 2
'endrow = xlLastRow("Sheet1")
'xllastrow is a function found at this forum otherwise use a number for testing
'Sheet1 is the name of your sheet
pos = 2
lv_item = 1
With ListView1
'gives me headers at the top
.View = lvwReport
'defining the columnheaders
With .ColumnHeaders
.Clear
.Add , , "Column 1", 60
.Add , , "Column 2", 60
.Add , , "Column 3", 60
.Add , , "Column 4", 60
End With
.HideColumnHeaders = False
.Appearance = cc3D
.FullRowSelect = True
For counting = startrow To endrow
If Worksheets("Sheet1").Range("B" & pos).Value > 1 Then
.ListItems.Add , , Worksheets("Sheet1").Range("A" & pos)
.ListItems(lv_item).ForeColor = RGB(255, 0, 0)
.ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("B" & pos)
.ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("C" & pos)
.ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("D" & pos)
Else
.ListItems.Add , , Worksheets("Sheet1").Range("A" & pos)
.ListItems(lv_item).ForeColor = RGB(0, 0, 0)
.ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("B" & pos)
.ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("C" & pos)
.ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("D" & pos)
lv_item = lv_item + 1
pos = pos + 1
End If
Next counting
End With
End Sub



Charlize

Charlize
09-08-2006, 04:36 AM
.ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("D" & pos))
The last ) must be deleted. So no )) but ). Sorry about that.
In if and else clause.

Charlize

Charlize
09-08-2006, 04:44 AM
Also End If forgotten. Sorry, I wasn't typing in the editor.

steve.t
11-08-2007, 05:21 PM
Charlize,

I used your code exactly as provided, but have a problem.

The ListView looks great, Headers exactly as expected, but I can't see any data at all. Is it an issue with ranges, or is it a Service Pack issue? Is your code for SP4 exclusively? I don't know what SP is installed on my machine, but it is ListView Control 6.0.

Regards,
Steve

Charlize
11-09-2007, 01:50 AM
Probably because endrow isn't declared. Under startrow = 2 you add this lineendrow = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Rowand the values in column B must be greater than 1