DTPicker1 Format

[Digitborn.com]

Hello,

How can I retrieve from UserForm DTPicker1 to a Cell only the time value?

I tried:

Private Sub CommandButton5P1_Click()
Dim MyDate1 As Date
MyDate1 = DTPicker1.Value

DTPicker1.Format = dtpTime

DTPicker1.Value = Format(Time, "Short Time")
'or
DTPicker1.Value = Format(MyDate1, "h:mm")

ws.Cells(lRow, 13).Value = MyDate1
'or
ws.Cells(lRow, 13).Value = DTPicker1.Value
End Sub

I really couldn't make it by myself, trying to read all kind of topics about DTPicker1 Format

The result is or:
12:00:00 AM - returns the defaul value in the cell

7/20/2007 11:44 - returns the date + the correct time filled in the UserForm

I'd like to return in the cell only the "h:mm".
Thanks in advance

 

[Norie]

roy

I think you might want to look further into using Custom Format.

I tried it and it seemed to be returning a date rather than time.

Also it only went from 00 to 11 then back to 00.

 

[Digitborn.com]

Norie, do you mean about this option:
.CustomFormat = "HH:mm tt"

because I fully tested the other things, and there's no any problem.

 

[Norie]

Eh, I thought you didn't want AM/PM.

 

[Digitborn.com]

No, I don't want AM/PM and it's fine with me. I'm just curious about Align Center for the control.

 

[Norie]

Well as far as I can see there are no alignment options for that control.

PS Did you see my comment regarding this not being a standard control?

 

[Digitborn.com]

Yes, I saw that too. Just thought that something's possible. You guys here have very very good knowledge in Excel and VBA.

 

[royUK]

If you are distributing this then Norie is correct, you could find problems on some machines.

When I set the DatePicker up using the code & write to a cell it does give the full date & time, but if you only want the time Format the entry

Cells(1, 1).Value = Format(Me.DTPicker1.Value, "hh:mm")

If you want an alternative add two comboboxs & a label to a userform. Then place this code in the initialise event

Private Sub UserForm_Initialize()
    Dim i      As Integer
    With Me
        For i = 1 To 12
            .ComboBox1.AddItem i
            .ComboBox1.Value = Format(Now, "hh")
        Next i
        For i = 1 To 60
            .ComboBox2.AddItem i
            .ComboBox2.Value = Right(Now, 2)
        Next i
        Label1.Caption = "current time is " & .ComboBox1.Value & ":" & .ComboBox2.Value

    End With
End Sub

 

[Digitborn.com]

PS Did you see my comment regarding this not being a standard control?

If you are distributing this then Norie is correct, you could find problems on some machines.

What do you mean by that? If the control is not included in your Microsoft Excel Package as a plugin?

Roy, I think that the alternative with 2 ComboBoxes and a Label is OK, but DTPicker is a very good control. I just wanted to make it TextAlignCenter somehow, but I couldn't. DTPicker does not have this property, it's not like in HTML