我在 Excel VBA 中的列表框中添加了 10 多列。我不断收到运行时错误"380"-无效的属性值。它正常工作,直到列表框中的第 9 列。我在其他任何地方都找不到任何合适的解决方案。有谁知道这个问题的解决方法?
Private Sub txtSearch_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Dim rng As Range
Set rng = Range("Lookup")
Dim rw
Dim strText As String
strText = LCase(txtSearch.Text)
With ListBox1
.RowSource = ""
.ColumnCount = 12
For Each rw In rng.Rows
If InStr(LCase(Cells(rw.Row, 4)), strText) Then
.AddItem Cells(rw.Row, 1).Value
.List(ListBox1.ListCount - 1, 1) = Cells(rw.Row, 2).Value
.List(ListBox1.ListCount - 1, 2) = Cells(rw.Row, 3).Value
.List(ListBox1.ListCount - 1, 3) = Cells(rw.Row, 4).Value
.List(ListBox1.ListCount - 1, 4) = Cells(rw.Row, 5).Value
.List(ListBox1.ListCount - 1, 5) = Cells(rw.Row, 6).Value
.List(ListBox1.ListCount - 1, 6) = Cells(rw.Row, 7).Value
.List(ListBox1.ListCount - 1, 7) = Cells(rw.Row, 8).Value
.List(ListBox1.ListCount - 1, 8) = Cells(rw.Row, 9).Value
.List(ListBox1.ListCount - 1, 9) = Cells(rw.Row, 10).Value
.List(ListBox1.ListCount - 1, 10) = Cells(rw.Row, 11).Value
.List(ListBox1.ListCount - 1, 11) = Cells(rw.Row, 12).Value
.List(ListBox1.ListCount - 1, 12) = Cells(rw.Row, 13).Value
End If
Next
End With
End Sub
我不知道这是否会解决所有问题,但它肯定会清理一下。 另外,我不确定您从哪个工作表中提取Cells(rw.Row, 2).value
。但它们可能与为什么它中途停止有关。 另外,要稍微清理一下,请尝试其他For Statement
。
Private Sub txtSearch_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim rng As Range: Set rng = Range("Lookup")
Dim rw
Dim strText As String: strText = LCase(txtSearch.Text)
With ListBox1
.RowSource = ""
.ColumnCount = 21
For Each rw In rng.Rows
If InStr(LCase(Cells(rw.Row, 4)), strText) Then
.AddItem Cells(rw.Row, 1).Value
For x = 1 To 12 '''Change Worksheet to your Worksheet name
.List(ListBox1.ListCount - 1, x) = Worksheets("Sample").Cells(rw.Row, x + 1).Value2
Next x
End If
Next
End With
End Sub
如果这没有帮助,请尝试@Cyril对数组所说的内容。
刚回到你身边...有点长,但这是一般的想法...
这一切都包含在 ActiveX 控件的代码中:
Option Explicit
Sub ListBox1_Click()
Dim rw As Range, strtext As String
Dim arr As Variant, ai As Long, aj As Long
Dim brr As Variant, bi As Long, bj As Long
strtext = "a" 'I used this when i did my testing
ReDim arr(11, 0)
For Each rw In Range("rng")
If InStr(LCase(rw.Value), strtext) Then
aj = findaj(arr)
If Not IsEmpty(arr(1, aj)) Then
aj = aj + 1
ReDim Preserve arr(11, aj)
End If
For ai = 1 To 11
arr(ai, aj) = Cells(rw.Row, ai + 1).Value
Next ai
End If
Next rw
ReDim brr(aj, 11)
For bi = 0 To aj
For bj = 1 To 11
brr(bi, bj) = arr(bj, bi)
Next bj
Next bi
ListBox1.ColumnCount = 11
ListBox1.List = brr
End Sub
Private Function findaj(ByVal brr As Variant)
Dim j As Long, meow As String
j = 0
Do While True
On Error GoTo toll
j = j + 1
meow = brr(1, j)
Loop
toll:
findaj = j - 1
End Function
所以这里发生了很多事情...我使用两个单独的数组,因为重新调暗数组在 VBA 中的工作方式。 您只能更新数组的第二个元素,因此arr(ai,aj)
只能在向数组添加新行时redim preserve
时更新aj
。
所以我们做了一个数组(arr
(,它根据VBA的限制来捕获数据。 在该数组中,我们使用一个函数 findaj
,它故意捕获错误以确定arr
中适当的最后一列(我将 column 的使用用斜体化,因为事实并非如此,但在考虑它时在空间上是有意义的(。
然后,按照列/行的适当顺序将数组arr
转换为brr
。
之后,你做你的.list = brr
.