AddItem with Multi Select Listbox values 1

[MrMode]

I have a multi select list box (simple), and want to push selections to another list box (value list) through AddItem code behind a command_click event.

I can do this for items selected when it is not a multi select list

Dim ItemIndex As Variant

For Each ItemIndex In Me.List18.ItemsSelected
    Me.List22.AddItem (Me.List18.Column(0) & ";" & Me.List18.Column(1))
Next

(I am also trying to get multiple columns for each record copied across.)

When I try and add "for each ..." logic, it only copies one record into the listbox?

   Dim valSelect As Variant
    Dim strValue As String 
          
        For Each valSelect In Me.List18.ItemsSelected
            strValue = strValue & "'" & Me.List18.ItemData(valSelect) & "'; "
        Next valSelect

Me.List22.AddItem strValue

What am I missing?

 

[missinglinq]

I don't believe you can use AddItem to add a group of items to the Listbox; think you have to add them one at a time, so your line

Me.List22.AddItem

needs to be within your For Each...Next construct. In other words, you need to add an item, then loop, and add another item, then loop again, and so forth.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[Andrzejek]

What do you get in the Immediate Window if you do this:

...[blue]
Debug.Print strValue[/blue]
Me.List22.AddItem strValue

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

As stated you need to create a string and do this in a loop
This moves choices between two like formatted listboxes (same columns) with any number of columns.

Public Sub MoveFromListToList(SourceList As ListBox, DestinationList As ListBox)
  Dim varItem As Variant
  Dim strAdd As String
  Dim columnCount As Integer
  Dim i As Integer
  columnCount = SourceList.columnCount
  For Each varItem In SourceList.ItemsSelected
     For i = 0 To columnCount - 1
        If strAdd = "" Then
           strAdd = SourceList.Column(i, varItem)
        Else
           strAdd = strAdd & ";" & SourceList.Column(i, varItem)
        End If
     Next i
      DestinationList.RowSourceType = "value list"
      DestinationList.AddItem strAdd
      strAdd = ""
  Next varItem
  'remove from source
  RemoveFromList SourceList
End Sub

Removing items from the multiselect is tricky. You cannot use the items selected property. As soon as you delete one from the list it deselects the items selected, and the collection becomes empty. You need to first save these to a temporary array or collection.

Public Sub RemoveFromList(SourceList As ListBox)
  Dim i As Integer
  Dim tempCol As New Collection
  Dim varitm As Variant
  'once you delete an item it clears the itemselected
  'so you need to store these values
  'must remove in descending order
  For Each varitm In SourceList.ItemsSelected
    tempCol.Add (varitm)
  Next
  For i = tempCol.Count To 1 Step -1
    SourceList.RemoveItem (tempCol(i))
  Next i
End Sub

You can then use it like this

Private Sub cmdMoveLeftToRight_Click()
  MoveFromListToList Me.lstOne, Me.lstTwo
End Sub
Private Sub cmdMoveRightToLeft_Click()
  MoveFromListToList Me.lstTwo, Me.lstOne
End Sub

 

[MrMode]

Thank you very much! That has solved the issue.

 

[MajP]

However, in your original code if you create the string like you did and use the row source instead of add item it should work.

Dim valSelect As Variant
    Dim strValue As String 
    For Each valSelect In Me.List18.ItemsSelected
       if strValue = "" then
         strValue =  "'" & Me.List18.ItemData(valSelect) & "'; " 
       else
          strValue = strValue & "'" & Me.List18.ItemData(valSelect) & "'; "
       end if 
       strValue = strValue & "'" & Me.List18.ItemData(valSelect) & "'; "
     Next valSelect
     me.List22.rowsource = strValue

The only issue would be that you may want to remove the final ';' in your string. Assuming a two column list your final string would look like
'John';'Smith';'Jim';'Brown';'Mary';'Black';