Hi,
I'm trying to create a search button that will populate my listbox and textbox if try searching for date range or by name only or by date only or by name and date range. I already tried tweaking the codes but I cannot come up with the solution.
Here are my codes:
Code
Private Sub cmdFind_Click()
Dim DateRange As Range, rCl As Range, rng As Range, Dn As Range
Dim Date1 As Date, Date2 As Date
Dim iX As Integer
Dim strName As String
Dim nrows As Long
Set DateRange = Sheet2.Range("A1").CurrentRegion.Columns(4)
Me.ListBox1.Clear
strName = Me.txtName.Value
Date1 = CDate(Me.txtDate.Value)
Date2 = CDate(Me.EndDate.Value)
For Each rCl In DateRange.Cells
If (rCl.Value >= Date1 And rCl.Value <= Date2) And (rCl.Offset(0, -3).Value = strName) Then
If rCl.Value = "" Then
With Me.ListBox1
.AddItem Sheet2.Cells(rCl.Row, 1)
.List(.ListCount - 1, 1) = Sheet2.Cells(rCl.Row, 2)
.List(.ListCount - 1, 2) = Sheet2.Cells(rCl.Row, 3)
.List(.ListCount - 1, 3) = Sheet2.Cells(rCl.Row, 4)
.List(.ListCount - 1, 4) = Sheet2.Cells(rCl.Row, 5)
.List(.ListCount - 1, 5) = Format(Sheet2.Cells(rCl.Row, 6), "hh:mm:ss")
End With
End If
End If
Next rCl
End Sub
Private Sub EndDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.EndDate
If Len(.Value) = 0 Then Exit Sub
If Not IsDate(.Value) Then
MsgBox "Please enter a correctly formatted date"
.Value = Empty
.SetFocus
End If
Exit Sub
End With
End Sub
Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.txtDate
If Len(.Value) = 0 Then Exit Sub
If Not IsDate(.Value) Then
MsgBox "Please enter a correctly formatted date"
.Value = Empty
.SetFocus
End If
Exit Sub
End With
End Sub
Display More