If Userform ListBox contains specific item then change label background colour.

**jamieswift1977** · 09-05-2012, 06:47 AM

...and if not, keep or revert back to original colour. Does this make sense to anybody? Your help would be very much appreciated.

What I am trying to do is to find a specific value, in this case "Standard/National" upon entering the last of 3 listboxes (ListBox1) and changing the colour of the label's background (no caption) from grey to bright green in it exists, but back to grey if not (i.e. if the next search term is null).

Thanks very much in advance for any help. :o)

**JosephP** · 09-05-2012, 07:17 AM

maybe

    If UBound(Filter(Application.Transpose(Application.Index(Me.ListBox1.List, 0, 1)), "Standard/National")) > -1 Then
        Me.Label1.BackColor = vbRed
    End If

change the names and colors as desired

**jamieswift1977** · 09-05-2012, 07:41 AM

Hi JosephP,

Thanks so much for your speedy assistance! However I have installed the code and I'm getting a 'Run-time error '13': Type Mismatch' error dialogue box. I didn't need to change any names in your code as they are the same names as my ListBox and Label anyway. Any ideas what could be doing this? Also does this need to be in the 'ListBox1_Change' sub or in the Userform_Initialize sub?

Thanks again!
Jamie

**JosephP** · 09-05-2012, 07:43 AM

it depends when you want it to run but the listbox must be populated when it runs
if it still errors can you provide a sample workbook?

**jamieswift1977** · 09-05-2012, 08:57 AM

Hi JP,

Here it is, sorry for the delay - I had to make a new one (using exactly the same code) as the file was too large to upload. Your help and assistance is greatly appreciated!

Thanks
Jamie

EDIT: Also yes, the listboxes are all populated/populating. Thanks.

**JosephP** · 09-05-2012, 09:03 AM

there were no items in the list at that point. change the code to

Private Sub ListBox1_Change()
    Select Case Me.ListBox1.ListCount
        Case 0
            ' no processing
        Case 1
            If Me.ListBox1.List(0) = "Standard/National" Then Me.Label1.BackColor = vbRed
        Case Else
            If UBound(Filter(Application.Transpose(Application.Index(Me.ListBox1.List, 0, 1)), "Standard/National")) > -1 Then
                Me.Label1.BackColor = vbRed
                
            End If
    End Select
End Sub

to handle that and other eventualities :-)

**jamieswift1977** · 09-05-2012, 09:40 AM

Thanks JP, thank you SO much for this, you have been invaluable and you have saved me a lot of time! Just one thing though if you don't mind, the code works great but there is one thing it is not doing and that is reverting back to grey (or another colour of my choosing) if Standard/National is NOT in the list. Is this something easliy fixed or would I have to find different code for this?

Thanks again!
Jamie

**JosephP** · 09-05-2012, 09:44 AM

just need an else clause

Private Sub ListBox1_Change()
    Select Case Me.ListBox1.ListCount
        Case 0
            ' no processing
        Case 1
            If Me.ListBox1.List(0) = "Standard/National" Then
                Me.Label1.BackColor = vbRed
            Else
                Me.Label1.BackColor = &H8000000F
            End If
        Case Else
            If UBound(Filter(Application.Transpose(Application.Index(Me.ListBox1.List, 0, 1)), "Standard/National")) > -1 Then
                Me.Label1.BackColor = vbRed
            Else
                Me.Label1.BackColor = &H8000000F
            End If
    End Select
End Sub

**jamieswift1977** · 09-05-2012, 10:00 AM

Absolutely 100% Perfect, works beautifully! Thanks for all your help, have a great day!

**JosephP** · 09-05-2012, 10:08 AM

thank you-you too :-)

**jamieswift1977** · 09-05-2012, 12:52 PM

Hi JP, me again!

Sorry to bother you, don't worry the code works fine and I'm just wondering whether it needs a slight adjustment or whether I have a glitch in my Excel program... (probably the latter knowing my luck), but could you tell me if this sounds like a code problem please?

Here goes: When I'm running the UserForm all seems fine and it does what I requested (Picks out Standard/National from the list), however - what happens is that if my last Listbox contains more than one null enrty (i.e. 2 or more items that are NOT Standard/National), then the colours don't change... does this make sense to you? If so, do you think it's a code glitch or an Excel glitch?

Thanks in advance, and sorry to bother you again!

**JosephP** · 09-05-2012, 01:05 PM

could be either ;-)
can you post a sample that demonstrates the problem?

**jamieswift1977** · 09-06-2012, 05:30 AM

Hi JP, thanks! Okay, the problem is like this:

If 'Standard/National' in list then label colour changes to green (Correct)
If 'Standard National' in list with other entries then label changes to green (Correct)
If 'Standard National' NOT in list but 1 null entry is, label changes to red (Correct)
If 'Standard National' NOT in list but MORE THAN 1 null entry is, label does NOT change colour. (Incorrect)

So it's just the last of the above that is causing slight problems, I was just wondering if you knew of any code to fix this? Thanks again!

**JosephP** · 09-06-2012, 05:55 AM

two changes required

Private Sub ListBox1_Change()
    Dim n As Long
    Dim bMatch As Boolean
    Select Case Me.ListBox1.ListCount
        Case 0
            ' no processing
        Case 1
            If Me.ListBox1.List(0) = "Standard/National" Then
                Me.Label1.BackColor = vbRed
            Else
                Me.Label1.BackColor = &H8000000F
            End If
        Case Else
            bMatch = False
            With Me.ListBox1
                For n = LBound(.List) To UBound(.List)
                    If .List(n) = "Standard/National" Then
                        bMatch = True
                        Exit For
                    End If
                Next n
            End With
            If bMatch Then
                Me.Label1.BackColor = vbRed
            Else
                Me.Label1.BackColor = &H8000000F
            End If
    End Select
End Sub

and change this line in listboxp_change

            If .ListCount = 1 Then .ListIndex = 0

to

            If .ListCount >= 1 Then .ListIndex = 0

**jamieswift1977** · 09-06-2012, 06:32 AM

As always JP, you are a legend! That's it sorted completely now. I've thoroughly tested all the bad spots and they are working fine now, many thanks for your kind assistance and patience! I would like to buy you a beer, where can I send it to?

**JosephP** · 09-06-2012, 06:38 AM

next time I'm in London I'll let you know ;-)

If Userform ListBox contains specific item then change label background colour.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Re: If Userform ListBox contains specific item then change label background colour.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions