Add values from multiple cells to a Listbox and get the First selected value back

Question

I am currently using the below code to populate a (userform) listbox with items on loan based on a person's name(Column B). I need to also include the Item ID number as a reference for further functions. I am wondering what is the best approach to return two values from a row (Item name(Column C) and ID number(Column L)) based on a reference value (Person's name) into a listbox.

Private Sub cboName_Change()

    Me.ListItem.Clear

    Dim ws As Worksheet
    Dim i As Range
    Set ws = Worksheets("Loans")

       With ws
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
       End With

        Set i = ws.Range("B1:A" & LastRow)

         With i
            Set c = .Find(cboName.Value, LookIn:=xlValues, Lookat:=xlWhole)
            If Not c Is Nothing Then
            firstAddress = c.Address
        Do
            Set c = .FindNext(c)
            ListItem.AddItem .Cells(c.Row, 3).Value

        Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With

End Sub

If this question is very similar to others already asked (always getting in trouble for that), any direction would be appreciated - I may just need some context (very new at coding). Thanks!

will this work, `ListItem.AddItem .Cells(c.Row, 3).Value & ":" & .Cells(c.Row, 12).Value` ? — Mikku, Jul 26 '19 at 02:16
Listboxes can have multiple columns: https://stackoverflow.com/questions/11213962/vba-listbox-multicolumn-add — Tim Williams, Jul 26 '19 at 02:48
Thanks @Mikku, yes that works. However, I am wondering whether it may be more appropriate to use multiple columns within the listbox so that it is easier to refer to that value at a later time. Could those individual values be referred to later if separated by the " : " in the same listbox column? — jtm, Jul 26 '19 at 03:17
Yes, both ways it will work. You can use `|` or `:` to concatenate as I commented. And later you can use `Split(value, "|")(0)` to access the first value. This is less complicated than using ListBox with Multiple columns, so usually I prefer this. — Mikku, Jul 26 '19 at 03:21
Brilliant, I'll see how that goes! Thanks a lot @Mikku - your help is appreciated! — jtm, Jul 26 '19 at 04:04
@Mikku, is this the correct way of referencing? I am getting an out of range error `Find(Me.ListItem.Selected(Split(Value, ":")(0)))` — jtm, Jul 26 '19 at 06:20
No. You have to replace the `Value` in split with variable that have string value. Basically the Value there means a string. — Mikku, Jul 26 '19 at 06:31
@Mikku Right, sorry I completely missed that. If I run the code with a msgbox to display results, it simply returns 'true' instead of the ID number. Without the split, it returns the full concatenated value. Apologies for my lack of troubleshooting ability here. — jtm, Jul 26 '19 at 07:07
This is working for me `Split(ListBox1.list(ListBox1.ListIndex), ":")(0)` where ListBox1 is the Listbox on Userform — Mikku, Jul 26 '19 at 07:27

score 0 · Accepted Answer · answered Jul 26 '19 at 07:31

0

I 'll put up an answer for you:

There are 2 ways for this

Use Multicolumn ListBox
Concatenate you string

Second one I ll describe here as i prefer this one.

So in your code use: ListItem.AddItem .Cells(c.Row, 3).Value & ":" & .Cells(c.Row, 12).Value

Now to get the selected Item back use: Split(ListItem.list(ListItem.ListIndex), ":")(0)

So if you have Name in one cell & Mikku in the Other, Listbox will display

Name:Mikku

and the second Line of code will give you Name

Demo:

answered Jul 26 '19 at 07:31

Mikku

6,538
3
15
38

That's great, thanks for that! But one issue, when multiple selections are made, only the last clicked value shows. So, if I have two entries in the list say, `Mikku` and `Joel`, if i clicked on both values but 'Joel' was the last clicked, the message box would return `Joel Joel` – jtm Jul 26 '19 at 07:36
You will have to play with the code. I can't say that, unless I try. Try different scenarios, if it doesn't work out. Let me know, will find something that will work. Saving the Selected Results in a Array could be one. – Mikku Jul 26 '19 at 07:41
Will do, thanks for all your help thus far - extremely appreciated. I'll see how I go. – jtm Jul 26 '19 at 07:42
I have tried adding this split function into an array but it seems to be giving me the same problem of doubling up on values. It's strange, because without using the split, the values show correctly when selected. I can send through current array code if it helps. Thanks once again for any advice! – jtm Jul 29 '19 at 01:48
@JoelMcWilliam ,, Probably you should ask a new Question. :) – Mikku Jul 29 '19 at 02:52

Add values from multiple cells to a Listbox and get the First selected value back

1 Answers1