CODEWITHSUNDEEP
vbaexcellistbox
Markus
Markus

Reputation: 57

Excel multi-select, multi-column listboxes

I am having trouble coding a userform that takes the selected data from one multi-column Listbox and adds it to another Listbox on the same userfrom. after adding, the data is removed from the source Listbox

"ListBox" is where the data is located, and "listbox1" is where it is being added to.

Private Sub add_Click()

For i = 0 To ListBox.ListCount - 1
    If ListBox.Selected(i) = True Then
        NextEmpty = ListBox1.ListCount
        ListBox1.List(NextEmpty, 0) = ListBox.List(i, 0)
        ListBox1.List(NextEmpty, 1) = ListBox.List(i, 1)
        ListBox1.List(NextEmpty, 2) = ListBox.List(i, 2)
        ListBox.RemoveItem (i)
    End If
Next

End Sub

This code gives me a Run-time error '381' "Could not set the list property. Invalid property array index." I have done some looking around but can't seem to pinpoint how to use these properties correctly. Any help is greatly appreciated.

Upvotes: 1

Views: 1861

Answers (2)

Markus
Markus

Reputation: 57

In order to do this, like Daniel said, we need to use an add function. In the code below you can see how I used the .additem function in my with-block.
To remove the selection after moving it to a new Listbox, I run a backwards loop.
For i = MainListBox.ListCount - 1 To 0 Step -1

Private Sub add_Click()

Dim i As Integer
For i = 0 To MainListBox.ListCount - 1
    If MainListBox.Selected(i) Then
            With ListBox1
            .AddItem
            .List(.ListCount - 1, 0) = MainListBox.List(i, 0)
            .List(.ListCount - 1, 1) = MainListBox.List(i, 1)
            .List(.ListCount - 1, 2) = MainListBox.List(i, 2)
            End With
        End If
    Next i

For i = MainListBox.ListCount - 1 To 0 Step -1
    If MainListBox.Selected(i) Then
        MainListBox.RemoveItem (i)
    End If
Next i

End Sub

Upvotes: 2

Daniel Möller
Daniel Möller

Reputation: 86600

You cannot set a value to an index greater than the maximum in the list (and the maximum is exactly ListCount (Or ListCount-1 if zero based).

So, you must add the values with ListBox1.Add

Upvotes: 1

Related Questions