Merging MonthView & DTPicker Valuie into one cell

**adam2308** · 09-01-2009, 09:46 AM

I have a user form on the attached example and on the form i have 'Month View' calendar control and 'Date/Time Picker'.

I am trying to merge the valuses of both controls into one cell. I have tried doing this with the following code:

Please Login or Register  to view this content.

When doing this though, the cell that the form puts the date/time into formats to mm-dd-yyyy. I don't understand why though as the cell is formated as dd-mm-yyy and i have also tried to add this into the code above but still displays mm-dd-yyyy.

**Leith Ross** · 09-01-2009, 11:38 AM

Hello adam2308,

I removed some of your code and the data is now returned as dd-mm-yyyy in the cell. Also, I added the bold feature to your date. Now when the date is selected it is bold on the MonthView. Here is the UserForm Code. All changes have been added to the attached workbook.

Please Login or Register  to view this content.

**adam2308** · 09-01-2009, 11:54 AM

Thanks for this Leith, making the selected date on the calendar bold is really useful too.

I've tried your attachment and it seems the cell displays as the correct format of dd-mm-yyyy when the date can not be transposed as mm-dd-yyyy, for example for 15th August 2009 will display as 15/08/2009. Although if the date can be transposed as mm-dd-yyyy, for example 10th August will be displayed as 08/10/2009.

Thanks for your help.

**Leith Ross** · 09-01-2009, 12:03 PM

Hello adam2308,

I am a little confused by what you want to accomplish. Can you explain it in more detail using some examples?

**adam2308** · 09-01-2009, 01:01 PM

ok... i've added a couple of examples and hopefully this will show what i mean. I tried to attach a couple of screenshots too, just in case something is working different between our computers but the file size was too bi to attach. Hopefully these attachments will explain what i mean though.

The first attachment (10th Aug) was entered off the calendar and it displayed wrong in the cell as 08-10-2009. The second attachment (15th Aug) displays correctly as 15-08-2009.

Does this make it clearer what i mean?

**Leith Ross** · 09-01-2009, 02:02 PM

Hello adam2308,

I see what you mean. VBA is US-Centric with its date format. On my machine, the date is correct when entered from the MonthView or manually in the Formula Bar. I can enter the date in the Formula Bar either as mm-dd-yyyy or dd-mm-yyyy. The result is always dd-mm-yyyy as the cell is formatted with this option. This looks like a version difference between US and UK versions of Excel. I really don't know what else to try.

**adam2308** · 09-01-2009, 05:28 PM

ok, thanks anyway. I think i'll revert back to this attached version using my own text boxes and spin buttons.

My only problems with this is that in would like each of the text boxes to loop around their range (hours text box 00-23 and mins text box 00-59). For example if the mins box was on 58 when i opened the form then i could use the up button on the spin buttons to quickly get to 01.

Also, the other problem i have with this version is that when i open the user form the hours and mins are filled from the last entered using the cells D2 and E2 but if for example i open the form and the hours from the last entry was 22 and i want to move it one hour to 23 the spin starts again from 00.

**Leith Ross** · 09-01-2009, 06:09 PM

Hello adam2308,

Here is the code for the UserForm. This shows the last time in the TextBoxes and initializes the SpinButton values. This has been added to the attached workbook.

Please Login or Register  to view this content.

**trucker10** · 09-01-2009, 06:26 PM

it also worked with ScrollBar

Please Login or Register  to view this content.

**adam2308** · 09-01-2009, 06:41 PM

Thanks guys, thats much better. Is there any way we can make the text boxes range loop around itself? I think once this is done, i'll be happy with form!!