If equals formula not working as expected

[jcornish]

Newish to VBA and have stumbled into a problem I can't crack myself.

What the code should do:
I have a series of list box (eTag1) items contained within a cell, which are comma delimited. I use the Split function and then two For loops to run through each of the items in the cell and compare them against the items in the list box (that already been initialised). If there is a match then the code marks the item in the list box as selected.

The problem:
This works fine for the first matched item from the cell, but the code doesn't work for any following matches. When I hover my cursor over Values1(i) and .eTag1.List(j) the tool tip shows the same value, but the next line of code is not executed and the if statement skips to the end.

Appreciate any help!</SPAN>

 Values1 = Split(Cells(RefRow, 11), ",")
                
            For i = 0 To UBound(Values1)
        
                For j = 0 To .eTag1.ListCount - 1
                
                    If Values1(i) = .eTag1.List(j) Then
                    
                    .eTag1.Selected(j) = True
                                            
                    End If
                
                Next j
             
            Next i

 

[Snakehips]

jcornish,

Welcome to MrExcel.

Check the ListBox properties and make sure that you have the MultiSelect property set to 1 not 0 ?

Hope that helps.

 

[jcornish]

Hi Tony,

Thanks for your prompt reply.

I have double checked that MultiSelect is set and it is. I use the same user form for data entry to write multiple listbox items to the same cell.

Thanks,

James

 

[Snakehips]

James,
Sorry, this isn't going to help a fat lot......
Your code works perfectly well for me both with a listbox in the sheet and a listbox on a form.

Have you got any stray leading / trailing spaces in the split values?

Maybe see if

  If Trim(Values1(i)) = .eTag1.List(j) Then

does it.

 

[jcornish]

Hi Tony,

That works perfectly!

The split must have been adding in an additional space.

James

 

[Snakehips]

That's great.
Chances are that your cell string is punctuated by 'comma&space' thus when split by comma every element after the first element will have a leading 'invisible' space.