Search for date in Listbox

[Pinkster69]

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
            c.Select
            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
                LoadPic
                
                
                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
            Do
                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
                        FindAll
                    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)
        Me.ForCollectionDataListBox.Clear
        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

 

[Tinbendr]

Try using strFind = CStr(Me.ForCollectionDTPicker.value)

 

[Pinkster69]

Thanks so much for your reply!

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

 

[Tinbendr]

Hmm, don't know. Maybe you can find an answer here. Look at the bottom of the page.

 

[Pinkster69]

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

 

[Pinkster69]

Read you link tried what it said and still no joy!

Bit of a Challenge this one hey??

 

[Pinkster69]

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!!