Transferring from both columns of a listbox to another

[Edward]

I have two two-column listboxes in a userform. I want to pass selected
elements of the first listbox to the other, but I can't seem to figure
out how to get both columns to pass.

The first column is a CAS registry number and the second column is the
Chemical Name. I want to be able to (multi)select these from the first
listbox (populated by a worksheet range referenced by the RowSource
property), run my code, and have these selected elements show up in
the second listbox exactly as in the first. Instead, I only get the
first column (the CAS registry number).

Here's my code:

================

Private Sub CmdAddCAS_Click()
Dim i As Integer
Dim j As Integer
Dim k As Integer

'Make sure something is selected
If LBoxCAS.ListIndex = -1 Then Exit Sub

'Make sure no duplicates
For j = 0 To LBoxCASSelected.ListCount - 1
For i = 0 To LBoxCAS.ListCount - 1
If LBoxCAS.Selected(i) Then
If LBoxCAS.List(i) = LBoxCASSelected.List(j) Then
Beep
Exit Sub
End If
End If
Next i
Next j

'Make transfer
For k = 0 To LBoxCAS.ListCount - 1
If LBoxCAS.Selected(k) Then
LBoxCASSelected.AddItem LBoxCAS.List(k)
End If
Next k
End Sub

=================

 

[Jim Cone]

You have to specify the column. First column is 0, second column is 1...
Dim k As Long
For k = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(k) Then
ListBox2.AddItem ListBox1.List(k, 0)
ListBox2.List(ListBox2.ListCount - 1, 1) = ListBox1.List(k, 1)
End If
Next k
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Edward" <[email protected]>
wrote in message
I have two two-column listboxes in a userform. I want to pass selected
elements of the first listbox to the other, but I can't seem to figure
out how to get both columns to pass.

The first column is a CAS registry number and the second column is the
Chemical Name. I want to be able to (multi)select these from the first
listbox (populated by a worksheet range referenced by the RowSource
property), run my code, and have these selected elements show up in
the second listbox exactly as in the first. Instead, I only get the
first column (the CAS registry number).

Here's my code:

================

Private Sub CmdAddCAS_Click()
Dim i As Integer
Dim j As Integer
Dim k As Integer

'Make sure something is selected
If LBoxCAS.ListIndex = -1 Then Exit Sub

'Make sure no duplicates
For j = 0 To LBoxCASSelected.ListCount - 1
For i = 0 To LBoxCAS.ListCount - 1
If LBoxCAS.Selected(i) Then
If LBoxCAS.List(i) = LBoxCASSelected.List(j) Then
Beep
Exit Sub
End If
End If
Next i
Next j

'Make transfer
For k = 0 To LBoxCAS.ListCount - 1
If LBoxCAS.Selected(k) Then
LBoxCASSelected.AddItem LBoxCAS.List(k)
End If
Next k
End Sub

=================