Populating ListBox. "Run-time error '380': Could not set the List property. Invalid property value."

Question

I always get error on column 11 when trying to populate ListBox.

I have excel table in sheet Evaluations with test data from B3 to P24. The first 4 columns are text data, remaining 11 columns are numbers between 1 and 100 The first row of the table contains in column 4 text - assessors name that is selected in combobox.

I have created Userform on which I have placed ListBox. I also have a combobox with Assessors names. I want to populate the ListBox only with the records where in column 4 is name of the Assessor selected in combobox.

Upon initializing UserForm I call sub FilterPopulateListBox.

    Private Sub FilterPopulateListBox()
        Dim PSelectedAssessor As String
        PSelectedAssessor = CmbAssessors.Value  ' Get the currently selected assessor's name
    
        Dim PWs As Worksheet
        Set PWs = ThisWorkbook.Sheets("Evaluations")
        Dim PLastRow As Long
        PLastRow = PWs.Cells(PWs.Rows.Count, "B").End(xlUp).Row
    
        Dim PData As Range
        Set PData = PWs.Range("B3:P" & PLastRow)
    
        ' Clear previous entries in the ListBox
        With lstEvaluations
            .Clear
            .ColumnCount = 15  ' Assuming there are 15 columns from B to P
            .ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50;50;50"
    
        
     Debug.Print lstEvaluations.ColumnCount  ' Should print 15
            
            ' Loop through each row in the range and add to ListBox if the fourth column matches
            Dim PRow As Range
            For Each PRow In PData.Rows
                If PRow.Cells(1, 4).Value = PSelectedAssessor Then  ' Check if fourth column matches the ComboBox
                    Dim PRowArray() As Variant
                    PRowArray = Application.Transpose(Application.Transpose(PRow.Value))
                    .AddItem PRowArray(1)  ' Add first column value
                    Dim i As Integer
                    For i = 1 To UBound(PRowArray)  ' Add other columns
                        .List(.ListCount - 1, i - 1) = CStr(PRowArray(i))
                    Next i
                End If
            Next PRow
        End With
    End Sub

on this line of the code

.List(.ListCount - 1, i - 1) = CStr(PRowArray(i))

I always get error when i=11

When i=11 the value of PRowArray(i) is 17 and the line

.List(.ListCount - 1, i - 1) = PRowArray(i)

shows error window

Run-time error '380':
Could not set the List property. Invalid property value.

When debugging when I point with cursor at the beginning of the line at .List(.ListCount - 1, i - 1) it says. "Could not get the List property. Invalid argument" but PRowArray(i) is showing value 17.

When i is less then 11 it shows eiter 'Null' (before executing that line - when the line is highlighted) or value of the PRowArray(i) when the line is extecuted and next line is highlighted.

• I have ensured that listbox has 15 columns.
• I have nested PRowArray(i) into CStr(PRowArray(i)) to solve if there were some I don't know what issues with data.
• I have debugged the code to see what kind of values the code reads for i 1 to 11. It all reads correctly even when i=11 and I point to PRowArray(i) the value is correctly 17

I read forums and I asked ChatGpt3.5

Answer 1

A Userform has not only a built-in limitation of 10 columns when using the .AddItem method. It even ignores it when the .ColumnWidth property is explicitly set to more columns - it'll assume a starting value of 10 columns only resulting in Error 380 ( List prop couldn't be set - Invalid property value).

Generally you can overcome this limitation by two ways:

• 1. Firstly by assigning a 2D-array to the .List (or .Column) property - this is called array method, c.f. my earlier post Populate Listbox with multiple columns for further in-depth details.
• 2. If you stick to .AddItem you can benefit from a very minimal array assignment to the .List property based on the actual columns count via the following procedure as initial code step after defining the wanted columns count:

Sub fixColumnCount(lbx As MSForms.ListBox, ByVal colCnt As Long)
'Auth.: T.M. (c) 2023-08
'Purp.: fix ColumnCount to an exact number (even greater than default of 10 cols) !!!!!!!!!!!!!
'Note:  example call (here: to number of column captions (Row 1:1) in given range)
'       fixColumnCount Me.ListBox1, rng.Columns.Count + 1

    With lbx
        .ColumnCount = colCnt
         '.Column = Split(String(.ColumnCount, " "), " ")
        .Column = Split(Space(.ColumnCount))       ' assign initial Array of empty strings to .Column prop
        .Clear
    End With
End Sub