asked on

Excel Forms DatePicker returns the wrong date

I have a form in Excel and on the form there is a Microsoft DatePicker control.

I set the VBA code of the form to call a function when the date changes but the previous date is sent and not the updated date is sent each time and not the new date that was changed.

I tried calling the function on exit and on change but neither of these helped.
here is the code I have in VBA:

'Private Sub wedding_date_picker_AfterUpdate()
'    Call updateWeddingPoints(wedding_date_picker.Value)
'End Sub

'Private Sub wedding_date_picker_Change()
'    Call updateWeddingPoints(wedding_date_picker.Value)
'End Sub
'
Private Sub wedding_date_picker_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call updateWeddingPoints(wedding_date_picker.Value)
End Sub
    
Private Sub cmbBarrower_Type_Change()
    If Not cmbBarrower_Type.Value = "" Then Call updateWeddingPoints(wedding_date_picker.Value)
End Sub

Private Sub updateWeddingPoints(ByVal wedding_date As Date)
    Set objZakaut = New Zakaut.Zakaut_calculations
    '******************
    'Debug
    MsgBox "The date being sent is: " & wedding_date
    '******************
    If cmbBarrower_Type.Value = "" Then
        MsgBox "öøéê ìáçåø àú ñåâ äìååä/äìååéí ëãé ìçùá ð÷åãåú òáåø ååú÷ äðéùåàéï"
    Else
        If Not (wedding_date = 0 Or wedding_date = "") Then
            wedding_date_results.Caption = objZakaut.Calculate_Mariage_Points(cmbBarrower_Type.Value, wedding_date)
        Else
            wedding_date_results.Caption = 0
        End If
    End If
    
    Set objZakaut = Nothing
End Sub

Open in new window

you can comment out line 18 and 23-33 to run this and see the problem

Roy Cox

I really would not use a DatePicker, they will not work on ll versions of Excel. There are several examples here

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

weisscoaching

ASKER

Thanks Roy,

Your Date Picker form is useful and works nicely.
I need to get the date choosen from into a form not into a spread sheet (at least to start with).
is this possible using your datePicker form (I couldn't get this to work myself)?

Roy Cox

There is an example there. In the calendarForm workbook that you have downloaded, open the VB Editor asnd run frmTest. When the form is open click into the TextBox and the Calendar Form shows. Select a date and it will populate the Textbox

weisscoaching

ASKER

OK I got it now.

Thank you this is useful.

Now I just need to change all my date picker fields to text fields that call this new functionality.
I go it working in one field so the rest is just a matter of setting it up.

Roy Cox

Not sure what you mean by changing to Text, surely dates should be formatted as dates.

weisscoaching

ASKER

of course the format of data in the field is Date but the fields I had set up in my form where all date pickers. now I need to change them to Text boxes and add the code that links them to the date picker form you provided that comes on field enter.

Roy Cox

I see what you mean

weisscoaching

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for weisscoaching's comment #a40861204

for the following reason:

These date pickers work just great.
I have replaced all the active X dtp objects with text boxes and calls to the date picker.

I have very pleased with its functionality, performance and flexability

Roy Cox

Surely the OP used my solution and CalendarForm example.

weisscoaching

ASKER

I pressed accept solution on a comment of mine when I meant to accept this option as the solution.

using the date pickers in your example has solve the issues for me