Combine dynamic listbox column values (1 Viewer)

[Mikki]

Good Day~
I have a form with two listboxes. Each list box is based on a query and changes values when selection is change. Only the first column is visible and there are 3 more columns I get values from to update a table. I need a vba code to get one listbox column values so I can put it in a sentence. If there is only one staff member I want to save one, if there is two I want to save want to put "and" between the first one and second one (John Doe and Jane Doe), or if there are 3 or more I want commas between and "and" at the end (John Doe, Jane Doe, and Sally Doe).
Please help.
Thank you in advance~mjc

 

[Mr. B]

Attached is a database that I put together that should do what you are wanting to do.

You can check the VBA code behind the form and various controls that make this work. From what I could tell from your post, you wanted to create a string of the names selected from non-displayed columns in a filtered list box. This demo data will do that but I have also included an option that will allow the user to select any listed staff title and the name associated with of the selected title will be added to the string and displayed on the form.

You will have to modify the code to fit your individual requirements.

 

[Mikki]

Thank you so much Mr. B
The database will not appear????
Please zip and upload.
Thanks so much~mikki

 

[Mr. B]

The attached database file downloads for me. Not sure what the problem can be. Check you private messages.

 

[Mikki]

Good Day Mr.B~
Based on your database sample, I achieved my code:
Private Sub SII_Click()
On Error GoTo Err_Handler
Dim ctlLB As Control
Dim strAstCount As Integer
Dim lngRow As Long
Dim strFull As String
Dim strFormal As String

Set ctlLB = Me!LstUnwanted

strAstCount = ctlLB.ItemsSelected.Count - 1

If strAstCount = 0 Then
strFull = ctlLB.ItemData(ctlLB.ItemsSelected(0))
strFormal = ctlLB.ItemData(ctlLB.ItemsSelected(0))
Else
With Me.LstUnwanted
For lngRow = 0 To .ListCount - 1
If .ListCount = 1 Then
strFull = .Column(0, lngRow)
strFormal = .Column(2, lngRow)
ElseIf .ListCount = 2 Then
If strFull = "" And strFormal = "" Then
strFull = strFull & .Column(0, lngRow)
strFormal = strFormal & .Column(2, lngRow)
Else
strFull = strFull & " and " & .Column(0, lngRow)
strFormal = strFormal & " and " & .Column(2, lngRow)
End If
Else
If strFull = "" And strFormal = "" Then
strFull = strFull & .Column(0, lngRow)
strFormal = strFormal & .Column(2, lngRow)
Else
If lngRow + 1 < .ListCount Then
strFull = strFull & ", " & .Column(0, lngRow)
strFormal = strFormal & ", " & .Column(2, lngRow)
Else
strFull = strFull & " and " & .Column(0, lngRow)
strFormal = strFormal & " and " & .Column(2, lngRow)
End If
End If
End If
Next lngRow
End With
End If


Me!Assit = strFull
Me!AAEIOASFormal = strFormal
End Sub
Thanks so much for all of the assistance~mjc

 

[Mr. B]

You are welcome. Glad it helped.