DTPicker Custom Format in Excel 2010

[RexJ]

We have an Excel spreadsheet that was originally developed in Excel 2003. Subsequently, it has been used with Excel 2007 and 2010, where several differences have been discovered.

The latest issue we are working on is an issue with the use of the DTPicker control.

This issue is with a control placed directly on a Worksheet, not with a UserForm in VBA.

The use case for this DTPicker is to allow the user to input a time between 00:00:00 and 23:59:59.

This is done using the DTPicker control with the Format set to 3 - dtpCustom and the CustomFormat set to HH:mm:ss (to use 24 hour time format).

This works great with Excel 2003. The control displays fine and the value in the field is formatted as 24 hour time.

It works OK in Excel 2007. There is some issue with the intial display where the control looks all garbled, but once it is refreshed (by simply scrolling in the worksheet) it displays fine, again with 24 hour time format.

Excel 2010 is a different story. In addition to the initial garbled display, the displayed format is in MM/DD/YYYY date format, not 24 hour time format.

I have worked on this for several hours and I can not find a way to programmatically overcome this issue.

I found there are 2 ways to manually change the properties of this control. After switching to Design Mode and selecting the DTPicker Control then right clicking, I can select the Properties for the control, or I can select the DTPicker Object->Properties. These are slightly different.


Using the DTPicker Object->Properties, if I:

1. select Design Mode
2. select DTPicker Object->Properties
3. select the 1 - dtpShortDate format, then OK
4. Exit Design Mode
5. scroll the worksheet to correct the garbled display
6. select Design Mode
7. select DTPicker Object->Properties
8. select the 2 - dtpTime format, then OK
9. Exit Design Mode
10. scroll the worksheet to correct the garbled display
11. select Design Mode
12. select DTPicker Object->Properties
13. select the 3 - dtpCustom format, then OK
14. Exit Design Mode
15. scroll the worksheet to correct the garbled display

then the DTPicker control displays the correct 24 hour, and the control functions correctly. However, once I save the file, exit, and reopen the file, it is back to displaying a MM/DD/YYYY format.

If I try to do the same sequence as above, only using the Properties (not the DTPIcker Object Properties), it does NOT work. As soon as I get to steps 13-15 and select the dtpCustom format it reverts to the MM/DD/YYYY format.

I also get the same behavior as the last paragraph if I try to programmatically change the DTPicker's format using VBA code. As soon as I set it to dtpCustom, it's right back to MM/DD/YYYY.

I am using file version 6.1.98.16 of the mscomct2.ocx file, which shows in the Excel VBA References as Microsoft Windows Common Controls-2 (SP6).

Sorry this posting is long, but I wanted to give as much info as possible.

Thanks in advance for any assistance.

 

[maykan]

Hey RexJ,

I had the same problem. You have to select the dtpTime format und choose a cell for output (eg. B12).
Then you take a blank cell (eg. C12) and connect it to B12 the following way =B12+0.
Format the Cell C12 with hh:mm und you will only get the desired time.