Using array to store selected items in list box

[melody.anne]

Hello! I need help with this: I want to use an array to store data from a list box into a variable. I want it to be able to store one value, or multiple values, depending on what is selected.

Main problem: this list box feeds off a table which has employee names and their e-mails. The list box itself only shows the names, and when I select what I want the array to store is their e-mails, not their names.

Dim strNames As String
Dim varItem As Variant
Dim intCount As Integer

For Each varItem In Me.lstNames.ItemsSelected
intCount = intCount + 1
Select Case Len(strNames)
Case 0
strNames = Me.lstNames.ItemData(varItem)
End Select
Next

MsgBox strNames

That code successfully displays the item I selected, but only displays the name. I need to make it look in the table and get me column #2. I also want it to be able to select more than one item at a time.

Some example code would be amazing, since I'm pretty new to this.

 

[pr2-eugin]

Simplest way is to change the Bound column to 2 rather than 1. Then you will collect emails instead of the names, with the current code.

 

[melody.anne]

Simplest way is to change the Bound column to 2 rather than 1. Then you will collect emails instead of the names, with the current code.

How do I incorporate this? I tried using a .Fields(2), which seemed to work with other things, without success.

 

[BlueIshDan]

Best way to approach this without running queries and what not every time is to select BOTH fields in order of name, email.

then set your column widths field to = ;0"

that way you are setting the first field to take what ever room it would naturally and hiding the second one.

With that you can select the second column

 

[pr2-eugin]

I tried using a .Fields(2), which seemed to work with other things, without success.

No ! That is not what I meant.

How do I incorporate this?

Go to the Form Design, click on the ListBox. On the Data tab, you will have the property "Bound Column". The value you currently have is 1, change that to 2. That is all. Then try running your code.

If you want to change your code, I think you want is .Column(1) not Fields(2)

 

[BlueIshDan]

    Dim i As Integer
    
    For i = 0 To lst.ItemsSelected.Count - 1
        MsgBox lst.Column(1, i)
    Next