Hodan
02-07-2017, 01:55 PM
Hello everyone,
I am trying to filter listbox in a userform using a checkbox but it throws a very strange error - at least for me. So I am population a listbox with 8 columns. The rows in those 8 columns has random values together with blank places - for example row 1 columns 1 to 6 has values and 7 and 8 are empty. I am trying to filter the listbox based on checkbox click event and I want to filter it based on the values in column 8. In my case I want all the rows which has values in them (some of the rows (rows are 601) in column 8 are empty and I want those to be removed). Everything works well till the point where it doesn't :( (it throws "Could not get the list property. Invalid property array index." error) - the strange thing is that it throws this error on a random row - sometimes it gets to row 271, every other time it gets to row 377 and so on throwing this error randomly for a certain row. Here is my code and I can't get it - what am I doing wrong?
Private Sub CheckBox2_Click()
Dim n, j As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
n = Me.lbxClients.ListCount - 1
If Me.CheckBox2 = True Then
For j = 0 To n
If Me.lbxClients.List(j, 7) = "" Then
Me.lbxClients.RemoveItem j
End If
Next j
End If
End Sub
I am not exactly master of VBA and I will appreciate any help. I suspect that I might be doing it completely wrong - so I accept any workaround. Thanks in advance to everyone.
I am trying to filter listbox in a userform using a checkbox but it throws a very strange error - at least for me. So I am population a listbox with 8 columns. The rows in those 8 columns has random values together with blank places - for example row 1 columns 1 to 6 has values and 7 and 8 are empty. I am trying to filter the listbox based on checkbox click event and I want to filter it based on the values in column 8. In my case I want all the rows which has values in them (some of the rows (rows are 601) in column 8 are empty and I want those to be removed). Everything works well till the point where it doesn't :( (it throws "Could not get the list property. Invalid property array index." error) - the strange thing is that it throws this error on a random row - sometimes it gets to row 271, every other time it gets to row 377 and so on throwing this error randomly for a certain row. Here is my code and I can't get it - what am I doing wrong?
Private Sub CheckBox2_Click()
Dim n, j As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
n = Me.lbxClients.ListCount - 1
If Me.CheckBox2 = True Then
For j = 0 To n
If Me.lbxClients.List(j, 7) = "" Then
Me.lbxClients.RemoveItem j
End If
Next j
End If
End Sub
I am not exactly master of VBA and I will appreciate any help. I suspect that I might be doing it completely wrong - so I accept any workaround. Thanks in advance to everyone.