excelvbalistboxuserform

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


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 read forums and I asked ChatGpt3.5


Solution

  • 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:

    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