Date Issues with User Forms

[Denny57]

Can someone please help with a persistant problem I have with using dates in VBA User Forms.

Requirement 1
To be able to input a date and other details into a user form which are then added into a worksheet

Requirement 2
To be able to search and recall these details back into the user form using the date detail.

Requirement 3
To be able to update recalled details and pass these updated details back to the worksheet using the date detail.

There are a number of other functions I will be looking to introduce but before I can consider these, I need to be able to add, search and update details using the date as the key.

I have tried various options to generate dates including using Date Picker and Text Boxes and have most recently tried searching by also using a Date Picker but the search is unable to find a match.

Private Sub cmdDateSearch_Click()
'Used to search for records for a specific date

Dim Res As Variant
Dim lastrow
Dim myFind As String

    Res = Application.Match(txtDateSearch, Sheets ("Daily Hours Input").Range ("A:A"), 0)
    If IsError(Res) Then
        MsgBox "Date Not Found", vbInformation, "Date Not Found"
    Call Userform_Initialize
    txtDateSearch.SetFocus
    Exit Sub
    End If

End Sub

This is the test code to see a match could be found when a date added to the worksheet using a Date Picker could be found by searching using the same Date Picker. Unfortunately the Message Box is always the result.
NOTE: The Range "A:A" has been used as the range in column A will constantly be increasing

Please can someone provide me with a solution which will enable me to achieve the three above requirements.

What would be useful would be to understand are
1) Which control is best to be used (Text Box or DTPicker)
2) Which format should be applied to the "Date" column in the worksheet
3) The appropriate code to apply to a text box to
a) Instruct a user to key a set format if a Text Box is to be used (eg dd/mm/yyyy which appears to be the format applied by the DTPicker)
b) To ensure the keyed input information is passed in a date format
c) To ensure the keyed search information is passed in the same date format
4) If it is possible to re-populate a DTPicker control with the date following a search

Many thanks

 

[RoryA]

You need to convert the text to a date and then to a number for best results:

Res = Application.Match(CDbl(CDate(txtDateSearch)), Sheets ("Daily Hours Input").Range ("A:A"), 0)

 

[dmt32]

What would be useful would be to understand are
1) Which control is best to be used (Text Box or DTPicker)

A date picker may be more helpful to users & give you application a more polished look but either method should be ok - just need to understand that the date placed in a textbox is text & you will need to take steps to coerce to a real date when posting from your userform to the worksheet

2) Which format should be applied to the "Date" column in the worksheet

really a personal choice - more important to ensure the date posted is a real date - you then apply the required number format to the range.

3) The appropriate code to apply to a text box to
a) Instruct a user to key a set format if a Text Box is to be used (eg dd/mm/yyyy which appears to be the format applied by the DTPicker)

Personally if users are entering dates directly to a textbox, I would not worry about what format they enter it in - just check that they have entered a valid date. You can apply the required display format in the textbox when they exit the control

b) To ensure the keyed input information is passed in a date format
c) To ensure the keyed search information is passed in the same date format

you can create a common code to validate date entry for each control

4) If it is possible to re-populate a DTPicker control with the date following a search

you would have to share your code with forum to to give practical suggestions that would work with your project

Following untested and only intended as an example to give you an idea of an approach you could consider. Others here may be able to offer alternative solutions.

Private Sub cmdDateSearch_Click()
    'Used to search for records for a specific date
    Dim rng         As Range
    Dim Res         As Variant, myFind As Variant
    
    Set rng = ThisWorkbook.Worksheets("Daily Hours Input").Range("A:A")
    
    myFind = Me.txtDateSearch.Value
    
    If Not IsDate(myFind) Then Exit Sub
    
    myFind = CDate(myFind)
    
    Res = Application.Match(CLng(myFind), rng, 0)
    
    If Not IsError(Res) Then
        
        MsgBox myFind & " Found"
        
    Else
        
        MsgBox "Date Not Found", vbInformation, "Date Not Found"
        
    End If
    
End Sub

Private Sub txtDateSearch_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Cancel = InValidDate(Me.txtDateSearch)
End Sub

'common function
Function InValidDate(ByVal Box As Object) As Boolean
    Const DateFormat As String = "dd/mm/yyyy"
    With Box
        If Len(.Value) > 0 Then
            If IsDate(.Value) Then
                'format textbox date
                .Value = Format(CDate(.Value), DateFormat)
             Else
                InValidDate = True
            End If
        End If
    End With
    If InValidDate Then MsgBox "Invalid Date Entry", 48, "Invalid Date"
End Function

If your users enter a date in say long format 10 October 2024 the Function will, when they exit the control, check that it is a valid date & if so, it will then apply your required display date format. If an invalid date is entered a msgbox is displayed and exiting the control is cancelled.

Hope Helpful

Dave

 

[Denny57]

Dave

Many thanks for all the assistance.

I am hoping someone might be able to help with another posting regarding using SUMIF with multiple criteria. I am working through 1004 issue as I can now get the code to work, however, it is creating a new record and not updating the original.
Thanks again

 

[dmt32]

Most welcome glad we were able to assist & your feedback much appreciated

I am sure someone will respond to your posts but be mindful this is a forum supported by Volunteers who freely give their time to assist & sometimes, may just have to be a little patient before you get a response.

Dave