Listbox Selections Not Taking

**wwalkerbout** · 03-10-2013, 03:44 PM

Hi,

I just asked a question about instantiating a Listbox object, which was quickly answered and resolved. Great help, AlphaFrog!

Now I have a following question. I am trying to set the selection in an existing Listbox based on a set of values I have in an array. My code seems to be writing the the '.list' which I understand is what's needed for a multi-select list, but the selections are not taking, when I view the Listbox.

My current code is:

                ' Team Member list work in progress.
                aryTeam = Range(TM).value
                With Sheets("Project_Creation")
                     ' Instantiate the replica Project Team Member selection Listbox.
                    Set lbTM = ActiveSheet.ListBoxes(CBR)
                    For p = 1 To 10
                        lbTM.List(p) = aryTeam(1, p)
                    Next p
                    Z = lbTM.List()
                End With

The 'Z' variable has been added just to test to see what is in the 'lbTM.List()' and my list of values displays. But when I look at the Listbox, nothing is selected.

Anyone know how to make a set of values display in a multi-select list box programmatically?

Cheers,

Wayne

**Norie** · 03-10-2013, 03:55 PM

Wayne

List doesn't select items in a listbox, you can try Selected for that.

I think you'll need to rejig your code though.

Something like this perhaps.

Dim Ans As Variant

' other code

For p = 1 To 10
     Ans = Application.Match(lbTM.List(p), aryTeam, 0)
     lbTM.Selected(I) = Not IsError(Ans)
Next p

What that is supposed to do is check using Application.Match if an item from lbTM is found in aryTeam, if it is then select the item.

That should work, though you might need to replace aryTeam with Range(TM).

**wwalkerbout** · 03-10-2013, 09:07 PM

Hi Norie,

Thanks for the very prompt response to my query. This is really helpful!

I can see that 'Application.Match(lbTM.List(p)' provides me with the full list of values in my Listbox that I can match against.

However, for some reason, where I use the aryTeam array or the reference to the cell range that holds my selected values (in a row range from 1 to 10 Team Members), I can't seem to get matching to identify the correct entries. I'm not sure what I'd doing wrong.

My current code is:

Dim Ans As Variant

    For t = 1 To UBound(aryTList)
        Ans = Application.Match(lbTM.List(t), Range(TM))
        lbTM.Selected(p) = Not IsError(Ans)
    Next t

An the Range displays the values I need selected.

I have also used an Array, which seems to by default create a multi-dimensional with a format of:

aryTeam(1,1) Name1
aryTeam(1,2) Name2
aryTeam(1,3) Name3
...

So I'm not sure with the array if I'm comparing the correct elements (or how to, more like it).

Any suggestions?

Cheers,

Wayne

**Norie** · 03-11-2013, 05:54 AM

Have a look at the variables in both your code and mine.

We both seem to have mixed things up.

**wwalkerbout** · 03-11-2013, 02:38 PM

Hi Norie,

Thanks for the further feedback. I must admin that I struggled with this all day yesterday (I'm a bit new to Excel Macros), and I finally gave up on comparison via the array. Instead, I used the values I had stored in cells in the Worksheet.

As I say, I'm not very experienced with coding, so I don't know how efficient the code below would be, but at least it works for me. I thought I'd share it in case someone else had the same need.

' Instantiate the Listbox to be updated with replica selections.
Set lbTM = ActiveSheet.ListBoxes(CBR)
' Set the Named Range of the Enterprise Resource Pool members list.
RP = "Table_LUT_Resource_Pool"
' Use an array for comparison of selected verses available Team Members.
aryTList = Range(RP).value
' Before setting the replica selections, run through as initialze all selections to none.
For u = 1 To UBound(aryTList)
    lbTM.Selected(u) = False
Next u
' Now run through and set the selections based on the replicated Project.
For s = 0 To 9
    x = Range(FM).Offset(0, s)
    For t = 1 To UBound(aryTList)
        y = aryTList(t, 1)
        If x = y Then
            lbTM.Selected(t) = True
            GoTo NextTM
        End If
    Next t
NextTM:
Next s

I also thought I'd share it in case someone might point me in the right direction on if there's a better way to code this.

Anyway, thanks for your support. It helped me get pointed in the right direction.

Cheers,

Wayne

**Norie** · 03-11-2013, 02:44 PM

Wayne

The array thing should've worked, but perhaps needed a bit of a tweak.

Didn't have anything to test with though.

PS Don't use Goto, use Exit For to exit the loop.

**wwalkerbout** · 03-11-2013, 03:38 PM

Hi Norie,

Thanks for the tip on the use of Exit in the For loop. Didn't know the right way to break there. I'll make the change in my code.

I think I would have finally gotten there with the array, but just found it easier to use the data I had stored in the sheet. The array was multi-dimensional.

Cheers,

Wayne