Consulting

Results 1 to 13 of 13

Thread: Change the color of text in a listbox based upon a certain criteria

  1. #1
    VBAX Newbie
    Joined
    Aug 2006
    Posts
    3
    Location

    Question Change the color of text in a listbox based upon a certain criteria

    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

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Newbie
    Joined
    Aug 2006
    Posts
    3
    Location
    So, is this a limitation in VBA? If yes, any other method could solve this problem?
    Thx a lot!!!

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Newbie
    Joined
    Aug 2006
    Posts
    3
    Location
    Thx a lot.

  6. #6
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    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.

  7. #7
    Malcolm and Norie sure spell "color" funny.

  8. #8
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Quote Originally Posted by Cyberdude
    Malcolm and Norie sure spell "color" funny.
    Ah, but correctly.

  9. #9
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location

    Listbox items with different colour based on certain criteria

    Quote Originally Posted by xacis
    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

    [vba]
    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

    [/vba]

    Charlize
    Last edited by mdmackillop; 09-08-2006 at 06:17 AM. Reason: Code changes implemented as noted below

  10. #10
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    [VBA].ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("D" & pos)) [/VBA]
    The last ) must be deleted. So no )) but ). Sorry about that.
    In if and else clause.

    Charlize

  11. #11
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    Also End If forgotten. Sorry, I wasn't typing in the editor.

  12. #12
    VBAX Regular
    Joined
    Nov 2007
    Posts
    15
    Location
    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

  13. #13
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    Probably because endrow isn't declared. Under [vba]startrow = 2[/vba] you add this line[vba]endrow = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row[/vba]and the values in column B must be greater than 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •