Listbox help

[dave8]

I have setup a Listbox with the following properties: fmListSyleOption, fmMultiSelectMulti, ColumnCount=3. Here is the code:

For r = 3 To rowCount
        If Sheets("Sheet1").Range("B" & r).Value = ComboBox1.Value Then
                    
           ListBox2.List(i, 0) = Sheets("Sheet1").Range("B" & r).Value
           ListBox2.List(i, 1) = Sheets("Sheet1").Range("C" & r).Value
           ListBox2.List(i, 2) = Sheets("Sheet1").Range("F" & r).Value
           i = i + 1
         End If
    Next

I'm getting an error that says, "Invalid property array index", when it tries to execute the first Listbox2.List statement. What is wrong and what am I missing?

 

[JLGWhiz]

I have setup a Listbox with the following properties: fmListSyleOption, fmMultiSelectMulti, ColumnCount=3. Here is the code:

For r = 3 To rowCount
        If Sheets("Sheet1").Range("B" & r).Value = ComboBox1.Value Then
                    
           ListBox2.List(i, 0) = Sheets("Sheet1").Range("B" & r).Value
           ListBox2.List(i, 1) = Sheets("Sheet1").Range("C" & r).Value
           ListBox2.List(i, 2) = Sheets("Sheet1").Range("F" & r).Value
           i = i + 1
         End If
    Next

I'm getting an error that says, "Invalid property array index", when it tries to execute the first Listbox2.List statement. What is wrong and what am I missing?

Perhaps you have it somewhere else in your code, but I don't see an initial value for "i" in the snippet posted. If i Is Nothing then the code errors. I assume you want i = 0 for the initial value, if you do then it should be declared before the For statement.

 

[mikerickson]

Is the .ColumnCount of the Listbox set to 3 or higher?

Also,

There might be issues if there aren't enough existing items in the List box.
To add those three lines to an empty ListBox (with .ColumnCount >=3)

For r = 3 To rowCount
        If Sheets("Sheet1").Range("B" & r).Value = ComboBox1.Value Then
                    
           ListBox2.AddItem Sheets("Sheet1").Range("B" & r).Value
           ListBox2.List(i, 1) = Sheets("Sheet1").Range("C" & r).Value
           ListBox2.List(i, 2) = Sheets("Sheet1").Range("F" & r).Value
           i = i + 1
         End If
    Next

 

[dave8]

The ColumnCount property is set to 3, but I don't see the values from Sheet1.Range("B").value; I only see the values from "C" and "F" of Sheet1. In other words, the first column in the Listbox is blank.

 

[dave8]

This works:

For r = 3 To rowCount
        If Sheets("Sheet1").Range("B" & r).Value = ComboBox1.Value Then
                    
           ListBox2.AddItem Sheets("Sheet1").Range("B" & r).Value

           ListBox2.List(i, 0) = Sheets("Sheet1").Range("B" & r).value
           ListBox2.List(i, 1) = Sheets("Sheet1").Range("C" & r).Value
           ListBox2.List(i, 2) = Sheets("Sheet1").Range("F" & r).Value
           i = i + 1
         End If
    Next

 

[mikerickson]

Unless its used later in the routine, you don't need the argument i

For r = 3 To rowCount
    If Sheets("Sheet1").Range("B" & r).Value = ComboBox1.Value Then
        With ListBox2
            .AddItem Sheets("Sheet1").Range("B" & r).Value
            .List(.ListCount - 1, 1) = Sheets("Sheet1").Range("C" & r).Value
            .List(.ListCount - 1, 2) = Sheets("Sheet1").Range("F" & r).Value
        End With
    End If
Next