doesn't populate all of dates in listbox based on condition in textbox

[abdelfattah]

Hello
I try to populate all of dates whether equal today or bigger than today in listbox based on textbox 1
so far just show date today
forMat date should be MM/DD/YYYY
this is the part of my code .

a = Len(Me.TextBox1.Text)
 
 If Left(Sheet1.Cells(ss, "d").Value, a) = Left(Me.TextBox1.Text, a) Then
 If a < Date Or a = Date Then
Me.ListBox1.AddItem Sheet1.Cells(ss, 1)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(ss, "e")
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = Sheet1.Cells(ss, "d")
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Sheet1.Cells(ss, "c")

any idea to fix it?

 

[sijpie]

If a < Date Or a = Date Then
    Me.ListBox1.AddItem Sheet1.Cells(ss, 1)
    for i = 5 to 7
             Me.ListBox1.AddItem Sheet1.Cells(ss, i)
    next i
end if

 

[abdelfattah]

thanks but it doesn't work . it will show all of data without any exception.

 

[dmt32]

Hi,
you are not sharing all the code so this is a bit of a guess but see if helps

Private Sub abdelfattah()
    Dim a           As Date
    Dim arr         As Variant
    Dim c           As Long, r As Long
    
    With Me.TextBox1
        If Not IsDate(.Text) Then Exit Sub Else a = DateValue(.Value)
    End With
    
    'intialize array
    '(assumes contiguous data starting on row 1 & sheet not protected)
    arr = Sheet1.Range("A1").CurrentRegion.Value

    With Me.ListBox1
        .Clear
        'loop array
        For r = 2 To UBound(arr, xlRows)
            If arr(r, 5) <= a Then
                .AddItem arr(r, 1)
                For c = 1 To 3
                    .List(.ListCount - 1, c) = arr(r, Choose(c, 5, 4, 3))
                Next c
            End If
        Next r
    End With
    
End Sub

Dave

 

[abdelfattah]

Hi Dave
doesn't work for me (it shows all of data without exception )

code

Private Sub CommandButton2_Click()
        With Me.ListBox1
            .ColumnCount = 5
            .ColumnWidths = "100;100;100;100;100"
        End With
Dim ls As Integer
Dim ss As Integer
 ls = Sheet1.Range("d10000").End(xlUp).Row
 Me.ListBox1.Clear
 For ss = 2 To ls
 a = Len(Me.TextBox1.Text)
 If a <= Date Then
 If Left(Sheet1.Cells(ss, "d").Value, a) = Left(Me.TextBox1.Text, a) Then
Me.ListBox1.AddItem Sheet1.Cells(ss, 1)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(ss, "e")
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = Sheet1.Cells(ss, "d")
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Sheet1.Cells(ss, "c")
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Sheet1.Cells(ss, "b")
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = Sheet1.Cells(ss, "a")
 End If
 End If
 Next ss
End Sub

data in sheet

date.xlsm
	A	B	C	D	E
1	customer	invoice no	invoice number	date	phone no
2	al1	125	1325	2/18/2016	12334
3	al2	126	1215	2/19/2016	12335
4	al3	127	236	2/20/2016	12336
5	al4	128	5566	2/11/2016	12337
6	al5	129	5236	2/20/2016	12338
7	al6	130	2254	2/19/2016	12339
8	al7	131	2532	10/12/2022	12340
9	al8	132	1252	5/18/2022	12341
10	al9	133	1256	8/9/2022	12342
11	al10	134	523	10/31/2022	12343
12	al11	135	2645	1/22/2023	12344
13	al12	136	1256	10/12/2022	12345
14	al13	137	5632	7/7/2023	12346
15	al14	138	154	9/28/2023	12347
16	al15	139	552	10/12/2022	12348
17	al16	140	5698	3/12/2024	12349
Sheet1

 

[dmt32]

try this correction to my code & see if resolves

Private Sub CommandButton2_Click()
    Dim a           As Date
    Dim arr         As Variant
    Dim c           As Long, r As Long
    
    With Me.TextBox1
        If Not IsDate(.Text) Then Exit Sub Else a = DateValue(.Value)
    End With
    
    'intialize array
    '(assumes contiguous data starting on row 1 & sheet not protected)
    arr = Sheet1.Range("A1").CurrentRegion.Value

    With Me.ListBox1
        .Clear
        'loop array
        For r = 2 To UBound(arr, xlRows)
            If arr(r, 4) <= a Then
                .AddItem arr(r, 1)
                For c = 1 To 4
                    .List(.ListCount - 1, c) = arr(r, Choose(c, 5, 4, 3, 2))
                Next c
            End If
        Next r
    End With
    
End Sub

Private Sub UserForm_Initialize()
        With Me.ListBox1
            .ColumnCount = 5
            .ColumnWidths = "100;100;100;100;100"
        End With
End Sub

Dave

 

[abdelfattah]

it works !
many thanks for your solution .

 

[dmt32]

it works !
many thanks for your solution .

welcome - appreciate your feedback

Dave