Can't added two columns from one listbox to two columns in another

[Webtechie]

Hello,

I have one multi-select two column listbox on a userform. I have a button
that will take the selection from the mult-select listbox that has two
columns to another listbox that also has two columns.

Here is my code:

Dim itemIndex As Integer
Dim myVar As Variant
With frmEmployeeMaintenance

.lstClubsAssigned.RowSource = "" 'second listbox

If .lstClubs.ListIndex = -1 Then 'first listbox
Exit Sub
End If

For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1
If .lstClubs.Selected(itemIndex) Then
.lstClubsAssigned.AddItem .lstClubs.Column(0, itemIndex) &
";" & .lstClubs.Column(1, itemIndex)
End If
Next itemIndex
End With


I found this code on a help forum, but it is not working for me. It only
adds a concatenated string to the first column. How do I add values to both
columns?

Thanks,

Tony

 

[Dave Peterson]

Try...

For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1
If .lstClubs.Selected(itemIndex) Then
.lstClubsAssigned.AddItem .lstClubs.list(itemindex, 0)
.lstclubsassigned(.lstclubassigned.listcount - 1, 1) _
= .lstclubs.list(itemindex, 1)
End If
Next itemIndex
End With

(Untested, uncompiled. Watch for typos.)

ps.

If this code is inside the userform module, I'd change this:
With frmEmployeeMaintenance
to
With Me

Me is the object that owns the code--in this case that userform.

 

[Webtechie]

Dave,

This line:

.lstclubsassigned(.lstclubassigned.listcount - 1, 1) _
= .lstclubs.list(itemindex, 1)

is throwing an error 13 - Type mismatch.

What is that about? It doesn't like the

.lstclubsassigned(.lstclubassigned.listcount - 1, 1).

I've added

..lstClubsAssigned.ColumnCount = 2

just to be sure it knows there are two columns. Same error.

Thanks,

Tony

 

[Dave Peterson]

I don't think it's the code.

I think it may be one of the settings you've made when designing the userform.

I created (and tested!) a small userform with 2 listboxes and a commandbutton.
This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()

Dim ItemIndex As Long

Me.lstClubsAssigned.Clear '???????
With Me.lstClubs
For ItemIndex = .ListCount - 1 To 0 Step -1
If .Selected(ItemIndex) = True Then
Me.lstClubsAssigned.AddItem .List(ItemIndex, 0)
Me.lstClubsAssigned.List(Me.lstClubsAssigned.ListCount - 1, 1) _
= .List(ItemIndex, 1)
End If
Next ItemIndex
End With

End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range
With Worksheets("Sheet1")
Set myRng = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.lstClubs
.RowSource = ""
.ColumnCount = myRng.Columns.Count
.MultiSelect = fmMultiSelectMulti
.List = myRng.Value
.ColumnWidths = "50;50"
End With

With Me.lstClubsAssigned
.RowSource = ""
.ColumnCount = myRng.Columns.Count
.MultiSelect = fmMultiSelectMulti
.ColumnWidths = "50;50"
End With

End Sub


========
One more thing to check.

Make sure you don't have the .rowsource assigned to the second listbox--either
in code or in the properties window. That doesn't play well with .additem.

But I'm not sure that would cause the error you're seeing.

 

[Webtechie]

Dave,

You were right. I had another procedure that was changing the properties of
the listbox. I had forgotten about that code.

I made some adjustments and everything is working fine now.

Thanks for pointing me in the right direction!

Tony