How To Filtering Datas On a ListBox By First Date and Last Date?

[Erdin? E. Ka]

Hi everyone,

I have some datas on a ListBox on UserForm1.
This ListBox (called ListBox1 ) includes 5 columns.
At 5th columns data type is date.

Also a have the DTPickers. ( called: DTPicker_First_Date and DTPicker_Last_Date )

I want to remove the all rows via a for-next loop, if date is in 5th column lower then DTPicker_First_Date or higher then DTPicker_Last_Date.


So, i need a some kind of filtering...

I worked on for solve it but i couldn't... Can someone help me please?

Thanks in advance.

[Erdin? E. Ka]

Hi everyone,

Today i posted a sample workbook for my problem.

My current codes are like this:

[VBA]
Private Sub CMB_Filter_Between_Two_Dates_Click()
Dim J As Long, Term_Date As Date, First_Date As Date, Last_Date As Date
First_Date = DTPicker_First_Date.Value
Last_Date = DTPicker_Last_Date.Value
For J = 0 To lbx_Suitable_Bank_Checks_And_Cert_of_Depts.ListCount
Term_Date = lbx_Suitable_Bank_Checks_And_Cert_of_Depts.List(J, 4)
If Term_Date < First_Date Or Term_Date > Last_Date Then
lbx_Suitable_Bank_Checks_And_Cert_of_Depts.RemoveItem (lbx_Suitable_Bank_Checks_And_Cert_of_Depts.ListIndex)
End If
Next J
End Sub
[/VBA]

But i have an error at this row:

lbx_Suitable_Bank_Checks_And_Cert_of_Depts.RemoveItem
(lbx_Suitable_Bank_Checks_And_Cert_of_Depts.ListIndex)

Can someone help me?

[Bob Phillips]

[vba]

Private Sub CMB_Filter_Between_Two_Dates_Click()
Dim J As Long, Term_Date As Date, First_Date As Date, Last_Date As Date
First_Date = DTPicker_First_Date.Value
Last_Date = DTPicker_Last_Date.Value
For J = lbx_Suitable_Bank_Checks_And_Cert_of_Depts.ListCount - 1 To 0 Step -1
Term_Date = lbx_Suitable_Bank_Checks_And_Cert_of_Depts.List(J, 4)
If Term_Date > First_Date And Term_Date < Last_Date Then
lbx_Suitable_Bank_Checks_And_Cert_of_Depts.RemoveItem J
End If
Next J
End Sub
[/vba]
But note that this will not work if you have bound the listbox to a worksheet range, you will need to remove it from the worksheet in this case.

[Erdin? E. Ka]

Hi Bob,

Thank you for intereset about me. But i think i solved the problem via the codes below...

Sorry, i didn't translate the names of the variables... But codes works fine.

[VBA]
Private Sub CMB_Iki_Tarih_Arasi_Suz_Click()
Dim Vade As Date, Ilk_Tarih As Date, Son_Tarih As Date, J As Long, Sayac As Long
On Error Resume Next
lbx_Benzer_Cek_ve_Senetler.MultiSelect = fmMultiSelectSingle
Ilk_Tarih = DTPicker_Ilk_Tarih.Value
Son_Tarih = DTPicker_Son_Tarih.Value
Sayac = 0
Birdaha:
For J = 0 To lbx_Benzer_Cek_ve_Senetler.ListCount
Vade = lbx_Benzer_Cek_ve_Senetler.List(J, 4)
If Vade < Ilk_Tarih Or Vade > Son_Tarih Then
lbx_Benzer_Cek_ve_Senetler.RemoveItem (J)
End If
Next J
For J = 0 To lbx_Benzer_Cek_ve_Senetler.ListCount
Vade = lbx_Benzer_Cek_ve_Senetler.List(J, 4)
If Vade < Ilk_Tarih Or Vade > Son_Tarih Then
Sayac = Sayac + 1
End If
Next J
If Sayac <> 0 Then
Sayac = 0
GoTo Birdaha
End If
End Sub
[/VBA]