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
I asked ChatGpt3.5
-
There is a limit for unbound data source. The max cols is 10.
-
Try to use an array to populate the listbox which has more than 10 cols.
Option Explicit
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(1)
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)
Dim arrRes(), iR As Long, i As Integer
ReDim arrRes(1 To 15, 1 To PData.Rows.Count)
' 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))
iR = iR + 1
For i = 1 To UBound(PRowArray) ' Add other columns
arrRes(i, iR) = CStr(PRowArray(i))
Next i
End If
Next PRow
ReDim Preserve arrRes(1 To 15, 1 To iR)
.Column = arrRes
End With
End Sub