filter with date and time format

[adam]

The following code does not filter data if I write date in textbox1 as 5/10/2022. my date format in column A is as 5/10/2022 1:07:19 PM.

How could I change the textbox1 line to achieve this?

Any help on this would be kindly appreciated.

Thanks in advance.

Code: Select all

Private Sub FilterData()
Dim Region As String
Dim Item_Type As String

Dim myDB As Range

With Me
If .TextBox1.Value < 0 Or .TextBox2.Value < 0 Then Exit Sub

Region = .TextBox1.Value
Item_Type = .TextBox2.Value
End With

With ActiveWorkbook.Sheets("mysheet")
Set myDB = .Range("A1:D1").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row)
End With

With myDB
.AutoFilter 'remove filters
.AutoFilter Field:=1, Criteria1:=Region ' filter data
.SpecialCells(xlCellTypeVisible).AutoFilter Field:=3, Criteria1:=Item_Type ' filter data again
Call UpdateListBox(Me.MyListbox, myDB, 1)
.AutoFilter
End With

End Sub

[HansV]

Region is a confusing name for a date variable. And you should really try indenting your code consistently.

Change the line

Code: Select all

.AutoFilter Field:=1, Criteria1:=Region

to

Code: Select all

.AutoFilter Field:=1, Criteria1:=">=" & Region, Operator:=xlAnd, Criteria2:="<" & Format(CDate(Region) + 1, "m/d/yyyy")

[adam]

Thanks for the help Hans. Region variable I have changed now and ill indent my code.

By the way, my code does populate the list box with worksheets headers. How could I avoid that and only populate the listbox with filtered data rows?

Code: Select all

Set myDB = .Range("A1:D1").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row)

[HansV]

Code: Select all

Call UpdateListBox(Me.MyListbox, myDB.Offset(1).Resize(myDB.Rows.Count - 1), 1)

[adam]

The code does not work with the modification.

Here's the second part of the code

Code: Select all

Sub UpdateListBox(MyListbox As MSForms.ListBox, myDB As Range, columnToList As Long)
Dim cell As Range, dataValues As Range

If myDB.SpecialCells(xlCellTypeVisible).Count > myDB.Columns.Count Then
    Set dataValues = myDB.Resize(myDB.Rows.Count + 1)
   MyListbox.Clear ' we clear the listbox before adding new elements
    For Each cell In dataValues.Columns(columnToList).SpecialCells(xlCellTypeVisible)
        With Me.MyListbox
        .AddItem cell.Value
        .List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
        .List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
        .List(.ListCount - 1, 3) = cell.Offset(0, 3).Value
        .List(.ListCount - 1, 4) = cell.Offset(0, 4).Value
        .List(.ListCount - 1, 5) = cell.Offset(0, 5).Value
        .List(.ListCount - 1, 6) = cell.Offset(0, 6).Value
        End With

    Next cell
Else
    MyListbox.Clear ' if no match then clear listbox
End If
MyListbox.SetFocus

End Sub

[HansV]

"does not work".
Do you get an error message? If so, what does it say, and which line causes the error?
Does the code produce an unexpected/incorrect result? If so, in what way?
Something else? If so, what?

[adam]

The listbox does not get populated with any data. It does not give any error or highlight any line of the code.

[HansV]

I'd have to see a copy of the workbook.

[adam]

See the attached file. The code works if the date and customer ID exists more than one. if the date and customer ID exists only once it doesn't work.

[HansV]

So you didn't rename Region and you didn't indent the code...

Here is a modified version.

Test.xlsm

[adam]

Sorry for that. I modified my old code. That's why the code uploaded was not indent and had date as region.