PDA

View Full Version : Fliter listbox using checkbox



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.

Leith Ross
02-07-2017, 02:33 PM
Hello Hodan,

When you remove tems from a List or delete cells in a Range, you should start at the end and go forward, like this...



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 = n To 0 Step -1
If Me.lbxClients.List(j, 7) = "" Then
Me.lbxClients.RemoveItem j
End If
Next j
End If

End Sub


However, if you have the ListBox linked to a Range, you can not delete or add items to the list.

Hodan
02-07-2017, 11:29 PM
Hi Leith and thanks for your answer,

It totally makes sense. Appreciate it - thanks a lot.


Regarding the items in the listbox - I don't want to edit the actual source - I just want to be able to filter the listbox, without touching the source.