Userform Date & Time Picker

Resolved · High Priority · Version 2010

Wed 28 May 2014, 15:59 Delegate Raani said...

Raani has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

Userform Date & Time Picker

Hi,

I have a userform with 4 DatePickers and when the command button is pressed the dates selected transfer to specified cells on the active worksheet.

However, when transferred, the dates are in Time format and not DD/MM/YYYY. I'm not sure what has gone wrong as it was working before in another userform and I simply copied over the code. I've tried formatting the cells the data transfers to but it reverts back to the Time format when the userform is completed.

I'm also having problems in setting the default date on the date pickers to the current date when the form is opened, I tried adding the following to the 'show form' code but it doesn't change

DTPicker1.value = Date

Could you tell me how to resolve these problems please.

Thanks

Raani

Fri 30 May 2014, 10:38 Trainer Gary said...

RE: Userform Date & Time Picker

Hi Raani

Thanks for getting in touch. I've not come across this particular issue before but try this: use the VBA Format function on the value.

So it would read

DTPicker1.Value = Format(Date, "dd/mm/yyyy")

See if that helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 3 Jun 2014, 12:36 Delegate Raani said...

RE: Userform Date & Time Picker

Hi Marius and Gary,

Thank you for your suggestions but these haven't fully resolved the problem.

I've set the DT picker so that it now shows the current date when the userform is loaded and it selects the dates correctly- thanks for this.

I've used the following code in the 'OK' command button to format the cell which the date selected in the form will be transferred to:

Sheets("Sheet1").Range("D35").NumberFormat = "dd/mm/yyyy"
Sheets("Sheet1").Range("D35") = DTPicker1.Value

However it returns the date as "00/01/1900"

Any ideas on this??

Thanks for our help

Raani

Fri 30 May 2014, 10:50 Trainer Marius said...

RE: Userform Date & Time Picker

Hello Raani,

Nice to hear from you. I've had a look at the datepicker tool and would like to suggest the following:

When you right-click the Userform Toolbox, select "Microsoft Date and Time Picker Control 6.0" from the list. This will place the Datepicker button in your toolbox. Use this control in your userform.

I've found that when I build this datepicker into my userform, it defaults to today's date.

All you need to do then is write some code to show the userform and to enter your selected date into a cell.

E.g. for the OK button:

Private Sub cmdOK_Click()

Range("A1") = DTPicker1.Value

Unload UserForm1

End Sub

and to show the userform:

Sub ShowDateForm()

UserForm1.Show

End Sub

I hope this helps.

Kind regards
Marius Barnard
Excel Trainer