Date/Time Picker

Hello adam2308,

This seems like a simple question with a simple answer. The question is simple but the answer isn't. Have a look a Chip Pearson's site about how to do this.

Form Positioner

There's an example of using comboboxes to add times here

Combo_Time.zip

Also, be aware that the Datepicker can be problematical when distributing your workbook if the end user does not have the control installed on their computer.

Hello adam2308,

Here is the code for the SpinButtons and the Enter button. This has already been added to the attached workbook.

Please Login or Register  to view this content.

Thank you for your replies. I particularly like Leith's version and would like to ask a few questions to improve further...

• Can i add code where if the user presses 'esc' button, the form closes.
• Format the hrs and mins text boxes to "00", so numbers like 1 becomes 01.
• Put a validation on the text boxes so the user can only enter 00-23 hours and 00-59 mins and also could this range loop around itself, i.e. if i use the spin buttons i can go up from 23 hours to 00 hours and so on.
• Have the ability to manually enter mins between the 5 mins increments and then be able to enter this into the cell value
• Enter button closes form once pressed.
• When calendar opens it shows the current date and time in the cell value.

I am finding the form positioner that was originally posted a little tricky to do so i wil come back to that once i have the form working as i want it to.

Also, if the computer i want to use the datepicker on on does not have this control installed can i install it on this computer so it can be used?

Thanks,
Adam.

Note: I have greyed out the points that have been resolved.

Thanks for your efforts Trucker10 but your version tries to close the workbook when pressing escape (i have found out how to do this now anyway) and showing the current time and date when opening the workbook is not what i am actually looking for. What i want there is when the form is opened that the calendar shows the date currently in cell B2, if no date is in the cell then todays date should be selected.

I have also fixed the form to close on pressing the enter button.

Note: I have greyed out the the bullet points above that have now been resolved.

Attached is the latest version where i am up to.

Originally Posted by adam2308

What i want there is when the form is opened that the calendar shows the date currently in cell B2, if no date is in the cell then todays date should be selected.

I had the wrong form translated into Dutch ( formulier ) and not user form
try this one

Please Login or Register  to view this content.

and for the user to close

Please Login or Register  to view this content.

Thanks for this Trucker.

Code:

Private Sub UserForm_Activate()
If IsDate(ActiveCell.Value) Then
MonthView1.Value = DateValue(Range("B2"))
Else
[B2].FormulaR1C1 = Format(Now, "dd-mm-yyyy hh:mm:ss")
End If
End Sub

This keeps the current date in the active cell on the date picker just fine. I would also like the hours and mins textboxes to show the current time. i.e if the date/time in cell B2 is "29/08/2009 20:35" then textbox1 would show "20" and textbox2 would show "35".

I altered the code to following...

Please Login or Register  to view this content.

This code works for textbox1 but of course textbox2 displays the month of the value in cell B2. Can i format textbox2 to just the mins "35"?

Attached is my latest version...
Thanks guys.

with 2 additional cells

thanks trucker.. thats great. I have merged your changes into my latest version. Below is the outstanding issues i have with my form and attached is the latest version, would appreciate it if anyone could help...

• Put a validation on the text boxes so the user can only enter 00-23 hours and 00-59 mins and also could this range loop around itself, i.e. if i use the spin buttons i can go up from 23 hours to 00 hours and so on.
• When the user form opens the hours and mins textboxes show the current values in cells D2 and E2 but if i use the spin buttons to move the hour or mins up the value starts again at zero rather than moving up one increment from the current value in the cells.

Can anyone help with these last two points? Any questions please ask.

Hello adam2308,,

Have a look at the attached workbook. Let me know if you need anything else changed or added.

Hi Leith,

Thanks for this, it seems you have managed to sort the problem we had with the date being converted to US style.

I still have a couple of little points i would like to fix if possible before i finish this thread if i may...

• On a previous version you added some code into the calendar that highlighted the selected date bold. Could add this back in as i quite liked that. Is it also possible to bold teh days of the week across the top of the calendar?
• I'd like it so that the minutes text box increased/decreased in 5 minute increments from whatever number is in the textbox, i.e. if the text box is on 43 and user presses the spin-button up, the minutes would go... 43, 48, 53, 58, 03 etc not 43, 45, 50, 55
• Is it possible to make the textboxes loop around the range set in them For example in the minutes box was on 53, i would like it i pressed spin-button up then the minutes textbox would go... 53, 58, 03, 08 etc.

Thanks.

Hello adam2308,

I think it is possible to do the TextBox loop. I'll need some time to play with it.

Hello adam2308,

OK, got the SpinButtons working in sync with the TextBoxes and the bold datre is back. Here is code for the UserForm.

Please Login or Register  to view this content.

Hello adam2308,

To increase the contrast of the weekday names, change the TitleBackColor property. You can also change the month/year color using the TitleForeColor property. In the attached workbook, white and green are used. Play around and see what color combinations you like best.

Hello adam2308,

I took more time to make some more improvements. The validation has been added to the hours and minutes. If the text box is empty a value of "00" is automatically entered. If the value is a negative number or greater than 23 for hours or 59 for minutes, an error message is displayed and focus is set back to the text box. I added icons to the Enter and Cancel buttons and accelerator keys. You can type ALT+C to Cancel and ALT+E to Enter the time. Here is the UserForm code complete with annotations.

Please Login or Register  to view this content.