could not set the list property. Invalid property value error

[vmjamshad]

Helow,
I am trying to populate a listbox if 3 criteria. If i add one column by using

Me.ListBox6.AddItem ws3.Cells(a, 2)

it is working.
But i have add multiple columns to the listbox. when I use

Me.ListBox6.AddItem
               'Me.ListBox6.List(b, 0) = ws3.Cells(a, 2)

I am getting an error 'could not set the list property. Invalid property value error'. Can you please help me to fix it. Please see the code below:

For a = 2 To lrow3
    If Me.ListBox1.Value = ws3.Cells(a, 5) Then
        If Me.ListBox2.Value = ws3.Cells(a, 7) Then
            If apNo = CStr(ws3.Cells(a, 6)) Then
                Me.ListBox6.AddItem ws3.Cells(a, 2)
               'Me.ListBox6.AddItem
               'Me.ListBox6.List(b, 0) = ws3.Cells(a, 2)
               'Me.ListBox6.List(b, 1) = ws3.Cells(a, 3)
               b = b + 1
            End If
        End If
    End If
Next a

[Aflatoon]

Have you set the column count to 2? What is in the cells when the error occurs?

[vmjamshad]

Have you set the column count to 2? What is in the cells when the error occurs?

Yes,

With ListBox6.Clear
.ColumnCount = 2
.ColumnWidths = "60;25"
End With

When I Debug, the error is in

Me.ListBox6.List(b, 0) = ws3.Cells(a, 2)

and if i run again from 1 level above

Me.ListBox6.AddItem

then executes without error. But the data is populated from the second row of the Listbox. hope that its clear.

[Aflatoon]

Did you set b to 1 somewhere at the start that you didn't show?

[vmjamshad]

Here is the full codes,

Private Sub ListBox3_Change()

Dim ws1 As Worksheet, ws2, ws3 As Worksheet, lRow As Integer, lrow2, lrow3 As Integer, a As Integer, ApartmentName As String, VillaType As String, VillaNumber As String
Dim apName, apTyp, apNo As String
Dim C1, C2, C3, C5, C7, C8, C10, C11, C12, C13, C19, b As Integer
Dim myCol As Collection
Set myCol = New Collection

Set ws1 = Sheet1
Set ws2 = Sheet2
Set ws3 = Sheet5

C1 = WorksheetFunction.Match("C1", ws1.Range("1:1"), 0)
C2 = WorksheetFunction.Match("C2", ws1.Range("1:1"), 0)
C3 = WorksheetFunction.Match("C3", ws1.Range("1:1"), 0)
C5 = WorksheetFunction.Match("C5", ws1.Range("1:1"), 0)
C7 = WorksheetFunction.Match("C7", ws1.Range("1:1"), 0)
C8 = WorksheetFunction.Match("C8", ws1.Range("1:1"), 0)
C10 = WorksheetFunction.Match("C10", ws1.Range("1:1"), 0)
C11 = WorksheetFunction.Match("C11", ws1.Range("1:1"), 0)
C12 = WorksheetFunction.Match("C12", ws1.Range("1:1"), 0)
C13 = WorksheetFunction.Match("C13", ws1.Range("1:1"), 0)
C19 = WorksheetFunction.Match("C19", ws1.Range("1:1"), 0)

Me.TextBox11 = ""
Me.TextBox12 = ""
Me.TextBox13 = ""
Me.TextBox14 = ""

ApartmentName = Me.ListBox1.Value
If Not IsNull(ListBox2.Value) Then
VillaType = Me.ListBox2.Value
End If
If Not IsNull(ListBox3.Value) Then
VillaNumber = Me.ListBox3.Value
End If

lRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
lrow2 = ws1.Cells(Rows.Count, C1).End(xlUp).Row

With ListBox4
.Clear
.ColumnCount = 13
.ColumnWidths = "60;25;25;30;35;190;140;180;25;40;40;40;40"
End With

For a = 6 To lrow2
    If ApartmentName = ws1.Cells(a, C1) Then
        If VillaType = ws1.Cells(a, C3) Then
            If VillaNumber = ws1.Cells(a, C2) Then
                ListBox4.AddItem
                 
                ListBox4.List(b, 0) = ws1.Cells(a, C8)
                ListBox4.List(b, 1) = ws1.Cells(a, C2)
                ListBox4.List(b, 2) = ws1.Cells(a, C3)
                ListBox4.List(b, 3) = ws1.Cells(a, C5)
                ListBox4.List(b, 4) = UCase(ws1.Cells(a, C7))
                ListBox4.List(b, 5) = ws1.Cells(a, C10)
                ListBox4.List(b, 6) = ws1.Cells(a, C11)
                ListBox4.List(b, 7) = ws1.Cells(a, C12)
                ListBox4.List(b, 8) = ws1.Cells(a, C13)
                ListBox4.List(b, 9) = ws1.Cells(a, C19)
                
                b = b + 1
            End If
        End If
    End If
Next a

apName = ListBox1.Value
apTyp = ListBox2.Value
If Not IsNull(ListBox3.Value) Then
apNo = ListBox3.Value
End If
lrow3 = ws3.Cells(Rows.Count, 1).End(xlUp).Row

With ListBox6
.Clear
.ColumnCount = 2
.ColumnWidths = "60;25"
End With

b = 1
Me.ListBox6.Clear
For a = 2 To lrow3
    If Me.ListBox1.Value = ws3.Cells(a, 5) Then
        If Me.ListBox2.Value = ws3.Cells(a, 7) Then
            If apNo = CStr(ws3.Cells(a, 6)) Then 'the data match till here
                'Me.ListBox6.AddItem ws3.Cells(a, 2)'if i run with this, then no error
               Me.ListBox6.AddItem
               Me.ListBox6.List(b, 0) = ws3.Cells(a, 2)'getting error here
               Me.ListBox6.List(b, 1) = ws3.Cells(a, 3)
               b = b + 1
            End If
        End If
    End If
Next a

End Sub

what I am trying to do is, trying to populate two different list boxes (listbox4 & listbox6) from two different sheets by changing 'Listbox3'. No issues with listbox4

[Bob Phillips]

Can you post a workbook to save us recreating data and such?

[vmjamshad]

WILL DO

[Aflatoon]

Change b=1 to b=0.

[vmjamshad]

Thanks a lot,