Problem with a nice Date Picker Form I had been using for years.

[mdd16]

Hi there,

I been using a downloaded Date Picker form which was working beautifully for several years.

I believe I can't send attachments here . so I will share the source here Trevor Eyre

However I can't get to load the form after I had to reinstall Excel recently.

Can someone examine this and help. It seems there is an issue with initialization commands.

Thanks in advance .

Best Regards

Mohan Dhingra`

 

[6StringJazzer]

Can you be more specific about what problem you are encountering? What steps are you taking, and what happens? Do you get any error messages? Assume we are doing this for the first time and tell us exactly what steps to take to reproduce your problem.

 

[mdd16]

Hello Jeff,

Thanks for the message.

I have a file loaded with several macros which I map to custom buttons on Ribbon. The file is located in Startup folder and opens every time excel opens. One of the buttons is mapped to the DatePicker Macro. The CalendarForm form is saved in VB project. This set up used to work well earlier but now after re-installation of Windows and Excel, when I run the Date Picker, the form does not show up, but the date gets entered in the selected cell as 00-Jan-00. Cell also gets number format as custom "dd-mmm-yy".

I cannot see the function GetDate in the list of User Defined Functions from button near formula bar.

Sub DatePicker()
Dim dateVariable As Date

dateVariable = CalendarForm.GetDate
Selection.value = Format(dateVariable, dd - mmm - yyyy)
Selection.NumberFormat = "dd-mmm-yy" ' "dd-mmm-yy, ddd"

End Sub

 

[6StringJazzer]

Why are you assigning a text string using Format into the cell, instead of directly assigning dateVariable to the cell as a date value? Also that line of code is invalid syntax. If that is exactly what you are doing , you are using
dd - mmm - yyyy
as a format, but it is not a format string. It is an arithmetic expression that evaluates to 0 because you are using three undeclared and undefined variables (you should use Option Explicit to identify this kind of error at compile time).

Try this and tell me what happens.

Sub DatePicker()
Dim dateVariable As Date

dateVariable = CalendarForm.GetDate
Selection.value = dateVariable
Selection.NumberFormat = "dd-mmm-yy" ' "dd-mmm-yy, ddd"

End Sub

If you still get 00-Jan-00 then there must be a problem in the date picker. I can test it in my own file, but if it works for me that won't help explain why it doesn't work for you.

Also what version of Excel are you using? Did you reinstall the same version you had before or a new version? I suggest you add that to your profile.

 

[mdd16]

Sub DatePicker() Dim dateVariable As Date dateVariable = CalendarForm.GetDate Selection.value = dateVariable Selection.NumberFormat = "dd-mmm-yy" ' "dd-mmm-yy, ddd" End Sub

Thanks for your advice Jeff. However, your suggested code still gives same result. Value is 0 only.

I believe the problem is with initialization and loading of the form. The form isn't showing up to create the datevalue

My excel version is 365 and the About Excel shows it to be Office16 program.

Thanks again.. do let me know if something else comes to your mind.

Best Regards

Mohan Dhingra`

 

[keithmct]

I use an addin called samradapps datepicker which works well for me.