PDA

View Full Version : Solved: DTPicker1 in Userform help



Mitchelson
05-16-2008, 10:14 AM
Hi All,

Please find attached a workbook with a UserForm I've created to pick the time and date.

8739

The code behide the UserForm as follows.

Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
ActiveCell.ClearContents
Unload Me
End Sub
Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
DTPicker1.Value = Date
End Sub
Private Sub Insert_Click()
If DTPicker1 <> "Select Desired Date" Then
ActiveCell.Value = DTPicker1.value & " " & LDTime1.TimeString
End If
Unload Me
End Sub
Private Sub UserForm_Click()
End Sub


This the code used to open the UserForm.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, Range("C6:F13")) Is Nothing Then
Cancel = True
Datetime1.Show
End If
End Sub


I've have a number of issues as listed below on how I would like the UserForm to run but do not have the VBA knowledge yet to make it work. Please let me know if I should split each one of the issues on to a separate post.

1) Want the DTPicker1 in the UserForm on open with today's date.
2) Can't get the Date from DTPicker1 and time LDTime1 entered in a single cell without the double spacing.
3) If you open the UserForm, don't select a date and click insert the date format is mm/dd/yyyy but if open the UserForm then select a date and click insert the date format is dd/mm/yyyy which is the format I need.

Any help recieved will be greatly appreciated.

Bob Phillips
05-16-2008, 10:48 AM
I couldn't get the object to load, DTPicker. Where did you get it from?

Mitchelson
05-16-2008, 11:13 AM
I found it in Additional Controls -> Microsoft Date and Time Picker Control 6.0 (SP6) (Location C:\WINDOWS\system32\mscomct2.ocx) It was already loaded on my work PC.

8744

I've attached the file but had to change the extention to xls so I could attach the file. Download file and change .xls to .ocx then add to C:\WINDOWS\system32. See if that works.

Bob Phillips
05-16-2008, 12:24 PM
I think I was meaning the other one. I have that date Picker, but not your time control.



1) Want the DTPicker1 in the UserForm on open with today's date.

Set the value property in the usefrom activate event



DTPicker1.Value = Date




2) Can't get the Date from DTPicker1 and time LDTime1 entered in a single cell without the double spacing.

What is the time control you are using?



3) If you open the UserForm, don't select a date and click insert the date format is mm/dd/yyyy but if open the UserForm then select a date and click insert the date format is dd/mm/yyyy which is the format I need.

Without the time control I am struggling with this one. When I just loaded the date, I got just the integer value, no formatting. You could try this though



Private Sub Insert_Click()
If DTPicker1 <> "Select Desired Date" Then
ActiveCell.Value = DTPicker1 & " " & LDTime1.TimeString
ActiveCell.NumberFormat = "dd/mm/yyyy hh:mm"
End If
Unload Me
End Sub


or maybe play with the format property, try 0 - long date.

Mitchelson
05-16-2008, 01:51 PM
Set the value property in the usefrom activate event

I'm still unsure where to insert value property code. If you try set the value in the Userform's properties to date you get the error message "Invalid property value"



The time control is just a simple time picker which you can achieve the same result by using a text box. I've try your code using DTPicker1 and a text box just entering the time as "08:00" but still getting the double spacing between the date and time.

See update workbook


8749

Bob Phillips
05-16-2008, 02:22 PM
You don't set the value in the control's property, but in the form activate



Private Sub UserForm_Activate()

DTPicker1.Value = Date
End Sub


Odd about the spaces, I have tried many different things, but there re always two spaces in the formula bar.

Mitchelson
05-17-2008, 03:44 AM
Thank you for explaining the form activates. The code works and it?s also solved the formatting issue when you opened the UserForm without changing the date, the output was mm/dd/yyyy but with the adding code the format is always dd/mm/yyyy.

I think that the double spacing between the date and time is just standard dd/mm/yyyy hh:mm formatting.