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