How do I properly add items in a multilistbox?

Question

I'm using vba to fetch equipment numbers and their corresponding information and putting them in a listbox. A user will enter in the equipment number they want and excel will fetch the info. However, when I click my 'Get Data' button the first time it works ok. When i do it the second time for another equipment number I get the message "Could not set the List property. Invalid property array index." Here's my code:

Dim value As Long

Public i As Integer

    Private Sub GetDataButton_Click()

    Dim num As Variant

value = EquipmentNumber.value

    For Each num In Sheets("S1 Cvtg Eqt List").Range(Range("B1"), Range("B1").End(xlDown))

        If num = value Then


            MWOList.AddItem (num)
            MWOList.List(i, 1) = (num.Offset(0, 1))
            MWOList.List(i, 2) = (num.Offset(0, 2))
            MWOList.List(i, 3) = (num.Offset(0, 3))
            MWOList.List(i, 4) = (num.Offset(0, 4))
            MWOList.List(i, 5) = (num.Offset(0, 5))

    i = i + 1

End If

 Next num

 i = i + 1

 End Sub

a) You can avoid variable `i` completely and use the list's `.ListCount` property instead after `.AddItem num`; as `.List` is zero-based subtracting `-1` results in the list's current row index: `MWOList.List(MWOList.ListCount - 1, i) = ...`; btw use `With MWOList` .. `End With` to shorten code; b) don't overload code with existing property or function names like `value`, use e.g. `myValue`. c) speed up code using arrays, d) prefer to declare range related counters as `Long` instead of `Integer` as rows counts over 1 million rows might exceed positive integer limit of +32k. - @JaylanPaige — T.M., Jun 25 '20 at 16:09
FYI Further links using array methods: [Populate listbox with multiple columns](https://stackoverflow.com/questions/47528558/vba-excel-populate-listbox-with-multiple-columns/47531440#47531440); [Speed up filling of listbox values in UserForms](https://stackoverflow.com/questions/46983374/how-to-speed-up-filling-of-listbox-values-on-userform-excel/47003993#47003993) - @JaylanPaige — T.M., Jun 25 '20 at 16:16

score 1 · Accepted Answer · 2020-06-19T16:14:05.117

Try below, please note that I had changed not only "i" declaration, and value to public, but also the List column position starts from 0, so if this is 6 element table, then switch it back.

The reason you had error was in fact another "i" iteration "i=i+1" after the loop, the list rows also start from 0, therefore you added 2nd index, and tried to insert it on the third position.

Public value As Long
Public i As Integer
Private Sub GetDataButton_Click()

Dim num As Variant

value = EquipmentNumber.value

For Each num In Sheets("S1 Cvtg Eqt List").Range(Range("B1"), Range("B1").End(xlDown))

If num = value Then
    i = MWOList.ListCount 'set i to available space
    MWOList.AddItem
    MWOList.List(i, 0) = (num.Offset(0, 1))
    MWOList.List(i, 1) = (num.Offset(0, 2))
    MWOList.List(i, 2) = (num.Offset(0, 3))
    MWOList.List(i, 3) = (num.Offset(0, 4))
    MWOList.List(i, 4) = (num.Offset(0, 5))

End If

Next num

EquipmentNumber = ""

End Sub

Right direction +:); suggest to declare variable `i` at procedure level instead of module level. btw you don't need brackets for the num.Value assignment. fyi c.f. further remarks in my comment to OP. — T.M., Jun 25 '20 at 17:06

How do I properly add items in a multilistbox?

1 Answers1