使用.AddItem和.List填充Excel用户窗体ListBox时触发运行时错误381

错误原因 #

ListBox的列表项索引是从0开始计数的，你的循环变量x从1开始，执行AddItem后，新添加的项对应的索引是x-1，但你用Me.ListBox1.List(x,2)去赋值，此时ListBox中还不存在索引为x的项，直接触发索引越界错误。

修正方案 #

方案1：调整索引匹配ListBox的0基计数 #

修改循环内的赋值语句，将索引改为x-1：

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
        Dim LastRow As Long ' 建议用Long避免行数过多溢出

        ' 避免Select操作，直接引用工作表
        With Sheets("Sheet10")
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With

        Me.ListBox1.Clear
    
        For x = 1 To LastRow
            Me.ListBox1.AddItem Sheets("Sheet10").Cells(x, "F").Value
            ' 修正索引为x-1，匹配ListBox的0基计数
            Me.ListBox1.List(x - 1, 2) = "test"
        Next x
    End With

End Sub

方案2：用数组批量填充（更高效） #

如果数据量较大，推荐用数组一次性填充ListBox，避免循环内逐个操作的性能损耗：

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

    Dim LastRow As Long
    Dim fillArr() As Variant
    Dim x As Long

    With Sheets("Sheet10")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' 初始化数组：LastRow行，3列
        ReDim fillArr(1 To LastRow, 1 To 3)
        For x = 1 To LastRow
            fillArr(x, 1) = .Cells(x, "F").Value ' 第1列对应ListBox的列0
            fillArr(x, 3) = "test" ' 第3列对应ListBox的列2
        Next x
    End With

    With Me.ListBox1
        .Clear
        ' 数组赋值给List，注意ListBox是0基，数组是1基的话会自动适配
        .List = fillArr
    End With

End Sub

额外优化点：

• 避免使用Select/ActiveSheet，直接通过工作表对象引用，减少运行错误
• 变量LastRow改用Long类型，避免行数超过Integer上限（65536）时溢出

内容的提问来源于stack exchange，提问作者Stephen Ord