PDA

View Full Version : [SOLVED] could not set the list property. Invalid property value error



vmjamshad
04-22-2018, 11:01 PM
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
04-23-2018, 01:10 AM
Have you set the column count to 2? What is in the cells when the error occurs?

vmjamshad
04-23-2018, 01:40 AM
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
04-23-2018, 01:49 AM
Did you set b to 1 somewhere at the start that you didn't show?

vmjamshad
04-23-2018, 02:06 AM
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

xld
04-23-2018, 02:26 AM
Can you post a workbook to save us recreating data and such?

vmjamshad
04-23-2018, 02:38 AM
WILL DO

Aflatoon
04-23-2018, 02:49 AM
Change b=1 to b=0.

vmjamshad
04-23-2018, 03:17 AM
:clap: Thanks a lot,