DTPicker1 Format

D

Digitborn.com

Active Member
Joined
Apr 3, 2007
Messages
353
Hello,

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

I tried:
Code: 
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 :rolleyes:
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Norie, do you mean about this option:
.CustomFormat = "HH:mm tt"

because I fully tested the other things, and there's no any problem.
 
Upvote 0
Eh, I thought you didn't want AM/PM.:eek:
 
Upvote 0
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?
 
Upvote 0
Yes, I saw that too. Just thought that something's possible. You guys here have very very good knowledge in Excel and VBA.
 
Upvote 0
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

Code: 
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

Code: 
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
 
Upvote 0
PS Did you see my comment regarding this not being a standard control?
Click to expand...

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

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 :(
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Application.Match error
Replies
4
Views
325
MARK858
MARK858
L
  • Question
VBA to export recurring meeting data
Replies
1
Views
535
John_w
J
P
  • Question
**Add existing date & time to another cell with condition**
2
Replies
16
Views
289
psc1
P
P
  • Solved
Time conversion to existing formula
2
Replies
13
Views
383
awoohaw
awoohaw
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
249
Gajendran Yadhav
G

Forum statistics

Threads
1,214,424
Messages
6,119,407
Members
448,894
Latest member
spenstar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top