excel - Populating Listbox with .Additem and .List - Run-time Error 381 - Stack Overflow

admin管理员组

文章数量:1404950

I am trying to populate a userform's listbox (Listbox1) in Excel with data from a worksheet of undetermined number of entries. I would then like to assign a one word description column to the list and this will change dependent upon a value in cells (x, "B") but for now I just want to create the listbox.
I am getting the

Run-time error 381: Could not set the list property invalid property array index

Debugging says the error is in

Me.Listbox1.List(x,2) = "test"

What am I missing? ListBox1 is set to multi columns =3.

Private Sub Userform_Initialize()

    ' FIll Destination Listbox
    With Me.Destination
        .List = Array("Printer", "Pdf", "Excel File")
        .ListIndex = 1
        .FontSize = 12
    End With

    ' FIll Report Type Listbox
    With Me.ReportType
        .List = Array("ALL", "Advance", "Ordinary", "SingleBuilding", "Mobile")
        .ListIndex = 2
        .FontSize = 12
    End With

    With Me.ListBox1
        Dim x As Integer

        Sheets("Sheet10").Select
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With

        Me.ListBox1.Clear
    
        For x = 1 To LastRow
            MsgBox x
            Me.ListBox1.AddItem Cells(x, "F")
            Me.ListBox1.List(x, 2) = "test"
        Next x
        
        Sheets("Staffing").Select
    End With

End Sub

I am trying to populate a userform's listbox (Listbox1) in Excel with data from a worksheet of undetermined number of entries. I would then like to assign a one word description column to the list and this will change dependent upon a value in cells (x, "B") but for now I just want to create the listbox.
I am getting the

Run-time error 381: Could not set the list property invalid property array index

Debugging says the error is in

Me.Listbox1.List(x,2) = "test"

What am I missing? ListBox1 is set to multi columns =3.

Private Sub Userform_Initialize()

    ' FIll Destination Listbox
    With Me.Destination
        .List = Array("Printer", "Pdf", "Excel File")
        .ListIndex = 1
        .FontSize = 12
    End With

    ' FIll Report Type Listbox
    With Me.ReportType
        .List = Array("ALL", "Advance", "Ordinary", "SingleBuilding", "Mobile")
        .ListIndex = 2
        .FontSize = 12
    End With

    With Me.ListBox1
        Dim x As Integer

        Sheets("Sheet10").Select
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With

        Me.ListBox1.Clear
    
        For x = 1 To LastRow
            MsgBox x
            Me.ListBox1.AddItem Cells(x, "F")
            Me.ListBox1.List(x, 2) = "test"
        Next x
        
        Sheets("Staffing").Select
    End With

End Sub

• excel
• vba

Share Improve this question Follow edited Mar 10 at 9:45 VBasic2008 55.4k55 gold badges2020 silver badges3636 bronze badges asked Mar 10 at 9:26 Stephen OrdStephen Ord 1111 silver badge22 bronze badges 1

• 1 Me.ListBox1.List(x-1, 1) = "test" – CDP1802 Commented Mar 10 at 10:19

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 3

As CDP1802 wrote in the comments, the runtime error occurs because you are using x as index and you start your loop with x=1, but the index of the List-property is 0-indexed.

With the first AddItem-Statement, List is a 2-dimensional array:

With Me.ListBox1
    .Clear
    Debug.Print LBound(.List, 1) & " to " & UBound(.List, 1) & " / " & LBound(.List, 2) & " to " & UBound(.List, 2)
    .AddItem "hallo"
    Debug.Print LBound(.List, 1) & " to " & UBound(.List, 1) & " / " & LBound(.List, 2) & " to " & UBound(.List, 2)
End With

That gives you:

0 to -1 / 0 to 9
0 to 0 / 0 to 9

As you can see:

• The first dimension (the rows) starts at 0. After adding the first item to the list, it has one entry (with index = 0)
• The second dimension contains always 10 elements from 0 to 9. It doesn't matter how you set ColumnCount of the listbox and there is no way to get more than 10 columns.

So the quick fix is to subtract 1 from x when writing the additional data fields, and as you want to write test propably into the second column, use 1 as second index:

For x = 1 To LastRow
    .AddItem Cells(x, "F")
    .List(x - 1, 1) = "test"
Next x

However, consider that one day you don't want to start at row 1 of your data, so it's maybe better to use the ListCount-property (and don't fet to subtract 1):

For x = 1 To LastRow
    .AddItem Cells(x, "F")
    .List(.ListCount - 1, 1) = "test"
Next x

---

And the obligatory hints:

o Don't use Select or Activate on sheets but qualify them:

Dim sourceSheet as Worksheet
Set sourceSheet = ThisWorkbook.Worksheets("Sheet10")
With sourceSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

(...)
AddItem SourceSheet.Cells(x, "F")

o Always use Option Explicit and declare all your variables

o Fet about Integer and always use Long:

Dim x As Long

List indexes start at 0.

    Dim x As Long, LastRow As Long, n As Long
    Me.ListBox1.Clear
    With Sheets("Sheet10")
       LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
       For x = 1 To LastRow
          Me.ListBox1.AddItem .Cells(x, "F")
          Me.ListBox1.List(n, 1) = "test"
          n = n + 1
       Next
    End With
    Sheets("Staffing").Select

本文标签： excelPopulating Listbox with Additem and ListRuntime Error 381Stack Overflow