Search for date in Listbox


New Member
Jun 19, 2012
Hi Guys ,

I have code that will look for any value in a Textbox and displays them in a listbox which works fine but I am having problems trying to find DATES using a the Datepicker "Drop down calender" when I Select a date and press the Find button I set up? I get no result what so ever, not even an error message. I think its the way I enter the "strFind = Me.ForCollectionDTPicker.Value" and some of the other values relating to the DTPicker on my code below "were ForCollectionDTPicker is the drop down calender" I use to set the date I want to find.
For your info I have sheet "2" with my data and Column "L" has all the different dates listed and is the column I am searching in. As mentioned above ForCollectionDTPicker is the item I use to pick the date i am looking for and "ForCollectionDataListBox.List" is the listbox

Hope someone can help?

Dim MyData     As RangeDim c          As Range
Dim rFound     As Range
Dim r          As Long
Dim rng        As Range

Dim imgFolder  As String        ' sub directory containing images
Dim sFileName  As String        'image name
Dim oCtrl      As MSForms.Control

Private Sub FindItemsCommandButton_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim rSearch As Range  'range to search
    Set rSearch = Sheet2.Range("L1", Range("l65536").End(xlUp)) 
    Dim f      As Integer

    imgFolder = ThisWorkbook.Path & Application.PathSeparator & "images" & Application.PathSeparator 'File were the images are kept
    strFind = Me.ForCollectionDTPicker.Value    'what to look for

    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            With Me    'load entry to form
                .SKUNumberTextBox3.Value = c.Offset(0, -3).Value
                .ColourTextBox3.Value = c.Offset(0, -2).Value
                .HireDaysTextBox3.Value = c.Offset(0, 3).Value
                .DateDueBackTextBox3.Value = c.Offset(0, 1).Value
                sFileName = c.Offset(0, -1).Value
                f = 0
             End With
       'counts the number of entries that are the same and displays them in a Message box with a "OK" or "Cancel" options to continue
        FirstAddress = c.Address
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f >= 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")

                    Case vbOK
                    Case vbCancel   'do nothing
                End Select
            End If
        'ElseIf CurioCardTextBox2.Value = "" Then: MsgBox strFind & " not listed"    'search failed
       End If
    End With

        If Sheet2.AutoFilterMode Then Sheet2.Range("L2").AutoFilter
End Sub

Sub FindAll()
    Dim strFind As String    'what to find
    Dim rFilter As Range     'range to search
    Set rFilter = Sheet2.Range("a1", Range("v65536").End(xlUp))
    Set rng = Sheet2.Range("a1", Range("a65536").End(xlUp))     'Range for the headings
    strFind = Me.ForCollectionDTPicker.Value
   'Sets the DataListBox2 ColumnCount to 22 as the properties panel only allows you 9 colouns in a List Box
    Dim myArray As Variant
    Me.ForCollectionDataListBox.ColumnCount = 22
    myArray = rng.Resize(, Me.ForCollectionDataListBox.ColumnCount).Value
    Me.ForCollectionDataListBox.List = myArray
    With Sheet2
        If Not .AutoFilterMode Then .Range("L2").AutoFilter
        rFilter.AutoFilter Field:=12, Criteria1:=strFind     'Must change the field value when looking for values in a differant column
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
        For Each c In rng
            With Me.ForCollectionDataListBox
                .AddItem c.Value
                .List(.ListCount - 1, 0) = Format(c.Offset(0, 0).Value, "dd/mm/yyyy")   'Converts the Date in the ListBox to Irish Date Format
                .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
                .List(.ListCount - 1, 5) = c.Offset(0, 5).Value
                .List(.ListCount - 1, 6) = c.Offset(0, 6).Value
                .List(.ListCount - 1, 7) = c.Offset(0, 7).Value
                .List(.ListCount - 1, 8) = c.Offset(0, 8).Value
                .List(.ListCount - 1, 9) = c.Offset(0, 9).Value
                .List(.ListCount - 1, 10) = c.Offset(0, 10).Value
                .List(.ListCount - 1, 11) = Format(c.Offset(0, 11).Value, "dd/mm/yyyy") 'Converts the Date in the ListBox to Irish Date Format
                .List(.ListCount - 1, 12) = Format(c.Offset(0, 12).Value, "dd/mm/yyyy") 'Converts the Date in the ListBox to Irish Date Format
                .List(.ListCount - 1, 13) = c.Offset(0, 13).Value
                .List(.ListCount - 1, 14) = c.Offset(0, 14).Value
                .List(.ListCount - 1, 15) = Format(c.Offset(0, 15).Value, "€#,##0.00")
                .List(.ListCount - 1, 16) = Format(c.Offset(0, 16).Value, "€#,##0.00")
                .List(.ListCount - 1, 17) = Format(c.Offset(0, 17).Value, "€#,##0.00")
                .List(.ListCount - 1, 18) = Format(c.Offset(0, 18).Value, "€#,##0.00")
                .List(.ListCount - 1, 19) = c.Offset(0, 19).Value
                .List(.ListCount - 1, 20) = Format(c.Offset(0, 20).Value, "dd/mm/yyyy")
                .List(.ListCount - 1, 21) = Format(c.Offset(0, 21).Value, "€#,##0.00")
            End With
        Next c
    End With
End Sub


Well-known Member
Jul 21, 2010
Try using strFind = CStr(Me.ForCollectionDTPicker.value)
Last edited:


New Member
Jun 19, 2012
Thanks so much for your reply!

Tried that but got a Compile Error "Method or Data member not Found"


New Member
Jun 19, 2012
Still no luck!

When I add a TextBox1 to the form "as a test" and change the CStr(Me.ForCollectionDTPicker.value) to Textbox1 and change a couple of the dates in Colomn L on my worksheet to numbers and change the format of those cells from "Date" to "General". It all works fine!
The only thing is that those numbers are displayed as dates in the listbox as I have formatted that way!

for the life of me, I cant figure it out


New Member
Jun 19, 2012
Read you link tried what it said and still no joy!

Bit of a Challenge this one hey??


New Member
Jun 19, 2012
So Guys,

Couldn't find anything to solve this issue but I found a bit of a work around!
As mentioned above I was using DTPicker for the Date so I created a New Textbox "TextBox1" and Entered "TextBox1.Value = ForCollectionDTPicker" in a Private Sub ForCollectionDTPicker_Change() module. So that when the Date is changed in the "ForCollectionDTPicker" it enters the date using the default Date format "US" in TextBox1.

Note: your probibly thinking why did I not change the format to "US" in the "ForCollectionDTPicker", well I did and nothing!! something to do with the "DTPicker built in format"??

I changed the strFind = CStr(Me.ForCollectionDTPicker.value) to strFind = TextBox1.Value were appropriate in the code and Hey Presto everything worked!

I suppose I could of created the Textbox in the first place instead of the DTPicker but you would have had to enter the date manually and that could cause conflict in finding the Date in question in the appropriate column on my worksheet as the code only recognizes the exact date etc, Hence the DTPicker..

Hope the above helps? Until someone comes up with a better solution when working with a DTPicker.

Thanks again to Tinbendr for all his help. Thanks dude!!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics