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
weisscoachingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I really would not use a DatePicker, they will not work on ll versions of Excel. There are several examples here
Roy CoxGroup Finance ManagerCommented:
Sorry. I clicked submit to soon. Here's the link to the examples, have a look and attach an example of your workbook if you want help using one of them

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
weisscoachingAuthor Commented:
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)?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Roy CoxGroup Finance ManagerCommented:
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
weisscoachingAuthor Commented:
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 CoxGroup Finance ManagerCommented:
Not sure what you mean by changing to Text, surely dates should be formatted as dates.
weisscoachingAuthor Commented:
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 CoxGroup Finance ManagerCommented:
I see what you mean
weisscoachingAuthor Commented:
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 CoxGroup Finance ManagerCommented:
Surely the OP used my solution and CalendarForm example.
weisscoachingAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.