Me.ListBox1.Clear生成运行时错误'- 2147467259(80004005)'：未指定的错误

Question

我们存货中有4000种不同的材料/设备。

我们使用一个VBA股票宏，与条形码扫描仪集成，使所有库存过程。

由于使用了VBA代码，我们在另一个工作簿中分别总结了所有不同的材料/设备(比如概要工作簿)。

要查看我们的股票中有多少不同的管道和多少米的管道，您应该单击汇总工作簿中的“管道”工作表。

对于“电器材料”、“法兰”、“配件”、“资产”等近20家股票集团来说是一样的。

所有的标题是分开的，它们是所有不同的页面作为一个列表。

我也列出所有的标题(“电气材料”，“法兰”，“配件”，“资产”，“管道”等)。到另一个工作表(比方说数据表)。

其主要思想是:使用此表作为数据列表。

所有上述操作的目的是检查材料/设备的数量和我们有多少不同的产品在我们的库存。但是，当您打开“摘要工作簿”时，检查是很复杂的。每个股票集团至少包括150种不同的材料/设备。

因此，我在摘要工作簿中创建了另一个工作表，并将其命名为主工作表。此外，我还创建了一个文本框和一个列表框。

我从(A2:F 4214)中选择数据表中的所有股票信息，并将它们命名为"DATA“。

因此，当我选择主表上的列表框时，我使用"ListFillRange“方法传输所有”数据“。

我使用6列和标题。

1-编号

2-条形码。

3-股票集团名称

4-股票名称

5-库存数量

6-存货计量(米、片、套、升等)

将textbox用作搜索框的代码：

Private Sub TextBox1_Change()

Dim i As Long
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, 1)
Me.ListBox1.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sayfa281.Range("D:D"))
a = Len(Me.TextBox1.Text)
If Sayfa281.Cells(i, 4).Value Like "*" & TextBox1.Text & "*" Then
Me.ListBox1.AddItem Sayfa281.Cells(i, 4).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Sayfa281.Cells(i, 4).Value
End If
Next i

End Sub

它提供了：

运行时错误'- 2147467259(80004005)'： 未指定的错误

当我单击DEBUG时，它以黄色显示Me.ListBox1.Clear。

当我在用户表单中使用上面的代码时，它工作了，但是在Excel表中，它没有工作。

Answer 1

对于列表框收缩错误，可以执行以下操作。

    ListBox1.Width = 1000
    ListBox1.Height = 800

就在离开潜艇之前。对我起作用了。

Answer 2

基于注释和这个mrexcel.com链接，80004005运行时错误似乎是由于使用.ListFillRange初始化列表框，它将列表框绑定到工作簿中的特定范围，并使从列表框中删除任何项(通过.RemoveItem或.Clear)是“非法”的。

如果不使用.ListFillRange，则必须手动配置列表框的列。下面是一些代码，可以在textbox的Change事件处理程序中使用来完成这一任务。这段代码有点通用，因此它可以很容易地调整到任何数据表。这段代码的一个更简单的版本只需将列表框的.ColumnWidths属性设置为一个硬编码字符串，这将基本上消除在Dim c as Long之后和Me.ListBox1.Clear之前对所有代码的需求，但我认为这段代码使列表框对源数据表中的更改更加灵活.

Private Sub TextBox1_Change()
    'To avoid any screen update until the process is finished
    Application.ScreenUpdating = False
    'This method must make sure to turn this property back to True before exiting by
    '  always going through the exit_sub label

    On Error GoTo err_sub

    'This will be the string to filter by
    Dim filterSt As String: filterSt = Me.TextBox1.Text & ""

    'This is the number of the column to filter by
    Const filterCol As Long = 4 'This number can be changed as needed

    'This is the sheet to load the listbox from
    Dim dataSh As Worksheet: Set dataSh = Worksheets("DataSheet") 'The sheet name can be changed as needed

    'This is the number of columns that will be loaded from the sheet (starting with column A)
    Const colCount As Long = 6 'This constant allows you to easily include more/less columns in future

    'Determining how far down the sheet we must go
    Dim usedRng As Range: Set usedRng = dataSh.UsedRange
    Dim lastRow As Long: lastRow = usedRng.Row - 1 + usedRng.Rows.Count

    Dim c As Long

    'Getting the total width of all the columns on the sheet
    Dim colsTotWidth As Double: colsTotWidth = 0
    For c = 1 To colCount
        colsTotWidth = colsTotWidth + dataSh.Columns(c).ColumnWidth
    Next

    'Determining the desired total width for all the columns in the listbox
    Dim widthToUse As Double
    'Not sure why, but subtracting 4 ensured that the horizontal scrollbar would not appear
    widthToUse = Me.ListBox1.Width - 4
    If widthToUse < 0 Then widthToUse = 0

    'Making the widths of the listbox columns proportional to the corresponding column widths on the sheet;
    '  thus, the listbox columns will automatically adjust if the column widths on the sheet are changed
    Dim colWidthSt As String: colWidthSt = "" 'This will be the string used to set the listbox's column widths
    Dim totW As Double: totW = 0
    For c = 1 To colCount
        Dim w As Double
        If c = colCount Then 'Use the remaining width for the last column
            w = widthToUse - totW
        Else 'Calculate a proportional width
            w = dataSh.Columns(c).ColumnWidth / colsTotWidth * widthToUse
        End If

        'Rounding to 0 decimals and using an integer to avoid localisation issues
        '  when converting the width to a string
        Dim wInt As Long: wInt = Round(w, 0)
        If wInt < 1 And w > 0 Then wInt = 1
        totW = totW + wInt

        If c > 1 Then colWidthSt = colWidthSt & ","
        colWidthSt = colWidthSt & wInt
    Next

    'Reset the listbox
    Me.ListBox1.Clear
    Me.ListBox1.ColumnCount = colCount
    Me.ListBox1.ColumnWidths = colWidthSt
    Me.ListBox1.ColumnHeads = False

    'Reading the entire data sheet into memory
    Dim dataArr As Variant: dataArr = dataSh.UsedRange
    If Not IsArray(dataArr) Then dataArr = dataSh.Range("A1:A2")

    'If filterCol is beyond the last column in the data sheet, leave the list blank and simply exit
    If filterCol > UBound(dataArr, 2) Then GoTo exit_sub 'Do not use Exit Sub here, since we must turn ScreenUpdating back on

    'This array will store the rows that meet the filter condition
    'NB: This array will store the data in transposed form (rows and columns inverted) so that it can be easily
    '    resized later using ReDim Preserve, which only allows you to resize the last dimension
    ReDim filteredArr(1 To colCount, 1 To UBound(dataArr, 1)) 'Make room for the maximum possible size
    Dim filteredCount As Long: filteredCount = 0

    'Copy the matching rows from [dataArr] to [filteredArr]
    'IMPORTANT ASSUMPTION: The first row on the sheet is a header row
    Dim r As Long
    For r = 1 To lastRow
        'The first row will always be added to give the listbox a header
        If r > 1 And InStr(1, dataArr(r, filterCol) & "", filterSt, vbTextCompare) = 0 Then
            GoTo continue_for_r
        End If

        'NB: The Like operator is not used above in case [filterSt] has wildcard characters in it
        '    Also, the filtering above is case-insensitive
        '    (if needed, it can be changed to case-sensitive by changing the last parameter to vbBinaryCompare)

        filteredCount = filteredCount + 1
        For c = 1 To colCount
            'Inverting rows and columns in [filteredArr] in preparation for the later ReDim Preserve
            filteredArr(c, filteredCount) = dataArr(r, c)
        Next

continue_for_r:
    Next

    'Copy [filteredArr] to the listbox, removing the excess rows first
    If filteredCount > 0 Then
        ReDim Preserve filteredArr(1 To colCount, 1 To filteredCount)
        Me.ListBox1.Column = filteredArr
        'Used .Column instead of .List above, as per advice at
        '  https://stackoverflow.com/questions/54204164/listbox-error-could-not-set-the-list-property-invalid-property-value/54206396#54206396
    End If

exit_sub:
    Application.ScreenUpdating = True
    Exit Sub

err_sub:
    MsgBox "Error " & Err.Number & vbCrLf & vbCrLf & Err.Description
    Resume exit_sub 'To make sure that screen updating is turned back on
End Sub

如果不再使用.ListFillRange，列表框将在开始时为空，只有在用户开始键入文本框后才会填充。当前，如果用户编辑并清除textbox，则整个数据表将加载到列表框中，但在重置列表框的代码块之后添加If filterSt = "" Then GoTo exit_sub可以轻松更改这种行为。

代码试图更快地加载数据，方法是在开始时将整个数据表读入内存，而不是一次读取一个单元格。它还避免使用listbox的.AddItem方法来一次加载整个列表，并绕过该方法的10列限制，就像在这个StackOverflow的答案中解释的那样(如果将来colCount的值增加，那么10列限制就会成为一个问题)。

代码使用两个数组。第一个数组将所有数据表行加载到内存中，第二个数组复制符合筛选条件的行。在第二个数组中，行和列是倒置的，这样就可以使用ReDim Preserve (在我们知道要保留在数组中的数据行的最终数量之后)轻松地调整其大小。之所以需要这种换位，是因为ReDim Preserve只允许您调整最后一个维度的大小，正如在这个StackOverflow的答案中所解释的那样。谢谢，@T.M.，谢谢你在这个StackOverflow的答案的建议！

Answer 3

对于像我这样的初学者来说，你无法想象你有多值得帮助。

非常感谢。

密码很好用。我也应该问你一件小事。

在所有类型的文本框中，我的列表框越来越小

事实仍然是，各种信息相互交织在一起。

我试图在代码下面更改一些参数，

   'Determining the desired total width for all the columns in the Listbox
    Dim widthToUse As Double
    'Not sure why, but subtracting 4 ensured that the horizontal scrollbar would not appear
    widthToUse = Me.ListBox1.Width - 4
    If widthToUse < 0 Then widthToUse = 0

    'Making the widths of the Listbox columns proportional to the corresponding column widths on the sheet;
    '  thus, the Listbox columns will automatically adjust if the column widths on the sheet are changed
    Dim colWidthSt As String: colWidthSt = "" 'This will be the string used to set the list box's column widths
    Dim totW As Double: totW = 0
    For c = 1 To colCount
        Dim w As Double
        If c = colCount Then 'Use the remaining width for the last column
            w = widthToUse - totW
        Else 'Calculate a proportional width
            w = dataSh.Columns(c).ColumnWidth / colsTotWidth * widthToUse
        End If

我做不到。你对此有什么建议吗？

祝你今天愉快。