How to fix " Run-time error '380' in Excel VBA?

Viewed 2230

I am adding more than 10 columns to my listbox in Excel VBA. I keep getting run-time error '380'-Invalid property value. It works properly until column 9 in listbox. I couldn`t find any proper solution for this anywhere else. Does anyone know a workaround for this issue?

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
2 Answers

I don't know if this will fix everything, but it will definitely clean it up a bit. Also, I am not sure what worksheet you are pulling Cells(rw.Row, 2).value from. But they may have something to do with why it stops part way through. Also, to clean it up a bit, try an additional 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

If this doesn't help, try what @Cyril said with the array.

Just came back to you... a bit long, but here's the general thoughts...

This all goes in the code for the ActiveX Control:

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

So there's a lot going on here... I use two separate arrays, due to how redimming arrays works in VBA. You can only update the second element of the array, so arr(ai,aj) can only have aj updated when I redim preserve while adding a new row to my array.

So we make an array (arr) that captures the data based on VBA's limitations. Within that array, we use a function, findaj, which intentionally traps an error to determine the appropriate last column in arr (i italicized the use of column, as it's not truly the case, but it makes sense spatially when thinking about it).

You then convert the array arr to brr in the appropriate order of columns/rows.

Afterwards, you make your .list = brr.

Related