Free Microsoft Excel 2013 Quick Reference

Using a Date time picker in a VBA form

I'm trying to create an input form that contains a Date and time picker
(DTPicker) to be distributed to different versions of Excel. I am using
excel 2000, however when I open it in Excel XP I get the following error
message:
Compile Error
Can't find project or library

And in Tools>References I get Missing: Microsoft Windows Common
Controls-2.6.0(SP4)

Is there an easy alternative instead of the DTPicker or some code to fix this?
I don't have control over other users having any particular add-ins/updates
applied unfortunately, or even what version of Excel or what install options
were chosen.

Any help would be much appreciated.


Post your answer or comment

comments powered by Disqus
I am using a Date/Time picker in my VBA UserForm. One thing I have noticed is that the dtpicker object remembers the "selection position" of the time element (hours/minutes/seconds/am-pm) that the user last modified in the field.

For example: if I change a time field to 01:10 PM and the last change I made was the :10 -- after I submit the form and then tab back into the field it will *remember* my cursor position on the :10 ... this is rather counter-intuitive ... one would expect that when you tab into the field you would always start at the hour (then arrow key over to the minutes and seconds) ... especially after a form submission.

Is there a way I can force this behavior ? Anytime the user tabs into the dtpicker field have it select/start at the hour and go from there?

Thanks in advance.

when i try to use a date time picker in an excel VBA, it
prompt me that it is not properly licensed.
I try to un-reg and then re-reg the mscomct2.ocx, but it
still cannot work.
Is there anybody know why ?!

Hiya,

I have a form with loads of textboxes on it that are linked (control source)
to a row on the spreadsheet. There is also a date time picker on this form.
The form is to enter AND to track timings of an aircraft turnaround on any
particular day. The default day for the date time picker is the current day.

I have 2 queries:

1. If the user changes the date on the date time picker, the linked cells
will automatically be changed to equal the timings on the selected date.
However, for some reason (and I assume it is because the cells are linked),
it will not allow me to enter dates in a text format into these cells (won't
even accept a TEXT(xxx,"hh:mm") formula - it just overwrites it with a time).
Thus, the textboxes on the form will always read an actual time, yet it will
show it as a decimal.

I have tried to include code to change the format of all the textboxes when
the datepicker is changed, but it only seems to work on enter/exit of the
textboxes (i.e. only of any use when they are entering data).

Here is my code:

Private Sub dtpdate_Change()

Dim i As Integer

Worksheets("Timings").Range("D1") = dtpdate
Worksheets("Timings").Range("B10:Y10").Copy
Worksheets("Timings").Range("B4").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

i = 1
Do Until i = 25
Me("txttime" & i) = Format(Me("txttime" & i).Value, "hh:mm")
'Doesn't work!
i = i + 1
Loop

End Sub

How can I get these damned textboxes to either show the times as in the time
format, or get the cells behind the textboxes to bloody accept times in a
text format?

2. I've considered using multiple date/time pickers (formatted to time)
instead of the textboxes, but have a really bug that it is annoying to enter
data into these - you cannot simply enter 2315 as it will not move from the
hours to the minutes on its own - it will simply overwrite the 23 with the
15. The only way I found around it is to either use the mouse or arrow key to
move to the minutes.
Is there a way to make data (time) entry easier on the picker?

Thanks for any help on this - it's been killing me for 2 days!!

Basil

Excel 2003, Windows XP

I am using the Date/Time Picker in a UserForm. The Change event will fire,
but neither the BeforeUpdate nor the AfterUpdate events will fire after
changing a date. Does anyone know why?

Thanks in advance.

Mark

I am using 2 date / time pickers in a userform.

This userform has a multipage with 4 pages (the date / time pickers are on
page 3).

Currently, I have code specifying what value the date/time pickers should go
to by default (the first is set to the first day of the current year and the
second is set to today).

With UserForm1
.DTPicker1.Value = DateSerial(Year(Date), 1, 1)
.DTPicker2.Value = DateSerial(Year(Date), Month(Date), Day(Date))
End With

The code to set the default value is part of the userform_initialize event.
However, when the event runs, I get an error stating "An error occured in a
call of the windows date and time picker control." So, the two controls end
up having values of 1/1/1900.

So, to see what the issue was, I put a button on the userform to update the
date / time picker values using the same exact code as I used in the event
initialize event. This works! However, I can't have users clicking buttons
to update what should be default values.

Any ideas? Is there a multipage select event? Any other way I can have
them automatically update without having the user do the work?

I am using 2 date / time pickers in a userform.

This userform has a multipage with 4 pages (the date / time pickers are on
page 3).

Currently, I have code specifying what value the date/time pickers should go
to by default (the first is set to the first day of the current year and the
second is set to today).

With UserForm1
.DTPicker1.Value = DateSerial(Year(Date), 1, 1)
.DTPicker2.Value = DateSerial(Year(Date), Month(Date), Day(Date))
End With

The code to set the default value is part of the userform_initialize event.
However, when the event runs, I get an error stating "An error occured in a
call of the windows date and time picker control." So, the two controls end
up having values of 1/1/1900.

So, to see what the issue was, I put a button on the userform to update the
date / time picker values using the same exact code as I used in the event
initialize event. This works! However, I can't have users clicking buttons
to update what should be default values.

Any ideas? Is there a multipage select event? Any other way I can have
them automatically update without having the user do the work?

Hi, I am trying to use miltiple date time pickers set to use the time as
their input from the user. My problem is I need to have some of them load on
the form in an active state with the check box visible, but not checked. So
if need be, the user can check the check box to activate the DTPicker.
I am using Excel 2003, VBA with ActiveX Date/Time picker Versin 6 (sp4).

This is a timesheet, collecting start and finish times from employees, so
Saturdays and Sundays will have the option to use or not, but the default
state is not active for weekend days.

Any help will be appreiciated.

Hi Jim

I would also be very interested it is a brilliant control - I am using Excel
2003 on my home PC I have the control and on my work PC I do not. Points of
note though - the object lives in the System32 folder and Microsoft have a
download (as I am sure your aware) but how could you deploy the control over
a network without visiting every single computer given the users would not
have access rights to add to a system folder?
Also, given the difficulties of working with dates (Well I have problems -
anyway!) why did Microsoft remove the control in the first place? Available
in 97 I believe.

Interested
Mark (InWales)

"Jim in Spokane" > wrote in message
...
> Wanted to add the error is missing this object: MSCOMCT2.OCX
>
> "Jim in Spokane" wrote:
>
>> I am using the Date Time Picker to select calendar dates using a VB form
>> Excel 2003
>>
>> It works great for me, however when I send the Excel File to other users
>> who
>> do not have Excel XP or 2003, they do not have the ActiveX Controls to
>> make
>> it work.
>>
>> Is there an easy way to point them to a link, where they can download the
>> ActiveX Controls necessary for the Date Time Picker?
>>
>> Thanks
>>

activesheet.oleobjects(1).object.value="10/10/2003"

Replace the 1 with whatever is the name of your dtp control

--

Regards,

Bill Lunney
www.billlunney.com

"Cathy Myers" > wrote in message
...
> Hi,
>
> How do I set the default value for a date/time picker
> using code. I am new to both Excel and VBA. Thanks.
>
> C.M.

I have a cell that reads "9/8/08". It is formatted as a date. I also have a
Userform. The control values are set by what is in the cells. For some
reason, I am getting an error on the line indicated below; "An error occured
in a call to the Window Date & Time Picker Control." I have the DTPicker
controls Format = 1 dtpShortDate. Anybody have any ideas?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

'autofills the user form with the data from global schedule worksheet
Cancel = True

With frmSalesSummary
.txbItemNumber = Cells(Target.Row, "A")
On Error Resume Next
.cboProductCode = Cells(Target.Row, "B")
.cboSalesPerson = Cells(Target.Row, "C")
.cboEngineer = Cells(Target.Row, "D")
On Error GoTo 0
.txbCustomer = Cells(Target.Row, "E")
.txbEndUser = Cells(Target.Row, "F")
.txbQty = Cells(Target.Row, "G")
.txbDescription1 = Cells(Target.Row, "H")
.txbDescription2 = Cells(Target.Row, "I")
.txbComments = Cells(Target.Row, "J")
On Error Resume Next
.cboShipMethod = Cells(Target.Row, "K")
.cboStatus = Cells(Target.Row, "L")
On Error GoTo 0
.dtpScheduledShip = Cells(Target.Row, "M")
.dtpActualShip = Cells(Target.Row, "N")
.txbBOM = Cells(Target.Row, "O")
.txbSalesPrice = Cells(Target.Row, "P")
.txbTotalEstHrs = Cells(Target.Row, "Q")
.txbTotalActHrs = Cells(Target.Row, "R")

' Engineering
If Not IsEmpty(Cells(Target.Row, "S")) Then
ERROR>> .dtpEngineering.Value = Cells(Target.Row, "S")
.txbEngEstHrs = Cells(Target.Row, "T")
.txbEngActHrs = Cells(Target.Row, "U")
.chkEngineering.Value = True
.chkEngineeringDone.Value = CBool(Cells(Target.Row,
"S").Font.Color)
' Call chkEngineering_Click
End If

End With

frmSalesSummary.Show

End Sub
--
Cheers,
Ryan

Hello
I am a District manager over 10 Retail stores. I have created a ms excel
xlt template that connects to a mdb by ODBC to pull reports. Some of the
reports include Time_Stamp. In the ms query builder i have used the
[(m/d/yyyy)] for the criteria. This works, but I have to type in the dates
when the 2 param dialog boxes opens. EXAMPLE: Start 11/15/2006 End
11/16/2006, To pull a report for 11/15/2006. I would really like to use MS
Access and then I would not have this problem but Corp is afraid that
employee's may corupt the mdb. This is why im using the Excel xlt to return
the reports to excel. My question is there anyway to have a Date Time Picker
to use for the Criteria in the ms query builder

Can anyone offer any help.

Hi,

In a Excel VBA macro, I have a Date Time Picker from which I want to
read and write dates.

Properties of my DTPicker control :

..customformat = "dd/MM/yyyy"
..checkbox = true

Sub save_detail()
Dim MyDate As Date
[...]
If IsDate(MainForm.txtMepDate.value) Then
MyDate = CDate(MainForm.txtMepDate.value)
Worksheets("donnees").Cells(flux_line, 50).value = MyDate
Else
Worksheets("donnees").Cells(flux_line, 50).value = ""
End If
[...]
End Sub

Sub init_detail(selected_flux As String)
Dim MyDate As Date
[...]
If IsDate(Worksheets("donnees").Cells(flux_line, 50).value)
Then
MyDate = CDate(Worksheets("donnees").Cells(flux_line,
50).value)
MainForm.txtMepDate.value = MyDate
Else
MainForm.txtMepDate.value = Null
End If

[...]
End Sub

The part "save_detail" works good : I find in my workbook cell a valid
date "dd/MM/yyyy hh:mm:ss"

But for the init_detail, it works good until I close the workbook and
reopen it.
Values saved before I close the workbook cannot be set with
init_detail, I have the error :

Error n°35788 : "An error occured in a call to the Windows Date and
Time Picker control"

I have tried many things I have read (window visible, change custom
format, etc.) but no solution.

Thanks for help

Hi,

In a Excel VBA macro, I have a Date Time Picker from which I want to
read and write dates.

Properties of my DTPicker control :

..customformat =3D "dd/MM/yyyy"
..checkbox =3D true

Sub save_detail()
Dim MyDate As Date
[=2E..]
If IsDate(MainForm.txtMepDate.value) Then
MyDate =3D CDate(MainForm.txtMepDate.value)
Worksheets("donnees").Cells(flux_line, 50).value =3D MyDate
Else
Worksheets("donnees").Cells(flux_line, 50).value =3D ""
End If
[=2E..]
End Sub

Sub init_detail(selected_flux As String)
Dim MyDate As Date
[=2E..]
If IsDate(Worksheets("donnees").Cells(flux_line, 50).value)
Then
MyDate =3D CDate(Worksheets("donnees").Cells(flux_line,
50).value)
MainForm.txtMepDate.value =3D MyDate
Else
MainForm.txtMepDate.value =3D Null
End If

[=2E..]
End Sub

The part "save_detail" works good : I find in my workbook cell a valid
date "dd/MM/yyyy hh:mm:ss"

But for the init_detail, it works good until I close the workbook and
reopen it.
Values saved before I close the workbook cannot be set with
init_detail, I have the error :

Error n=B035788 : "An error occured in a call to the Windows Date and
Time Picker control"

I have tried many things I have read (window visible, change custom
format, etc.) but no solution.

Thanks for help

I'm connecting to an Access Database using ADODB and would like to sum up the
total values for a Date/Time field called Duration.

The SQL I'm using to get this is: "SELECT SUM(Duration) FROM 3_REPORT;"

I am using Excel as my interface and when I run this SQL it returns this
value: "1.6937569511234E+190" regardless of how I format the cell. I was
expecting something like 24:14:01.

If I just cut and paste my data from Access into Excel, then use the excel
function so sum up the total duration, then it gives me what I want. I would
like to do this in the code though.

Any ideas what I'm doing wrong, or how to get around this?

Thanks in advance.

--
Rone

I am using the Date Time Picker to select calendar dates using a VB form
Excel 2003

It works great for me, however when I send the Excel File to other users who
do not have Excel XP or 2003, they do not have the ActiveX Controls to make
it work.

Is there an easy way to point them to a link, where they can download the
ActiveX Controls necessary for the Date Time Picker?

Thanks

Hi,

I'm trying to use the Date Time Picker control as a time picker and only
want hours and minutes to show. Can this be done with its custom format
property (Example please if so)? The usual number formating string doesn't
seem to work and I'm having trouble finding guidance in the help system.

Thanks in advance.

Best Regards,
Walt Weber

Hi All,

i have a very peculiar problem

i my work sheet i have a date time picker control. it works fine every
thing is working fine but...............

each time i open my work book this date time picker control will be
resized

(assume if the original height = 17.25 and width = 139.5 it is be
changed to height = 20 and width = 50)

if i move horizontal or vertical scroll bars the sizes get reseted to
original size.
this is making my work sheet look very ugly at the time of opening the
file.

Plz Plz Plz help me out to solve this probelm.
1. How to make my date time picker size unchanged during opening of the
file.

--
sjayar
------------------------------------------------------------------------
sjayar's Profile: http://www.excelforum.com/member.php...o&userid=28435
View this thread: http://www.excelforum.com/showthread...hreadid=484201

Hi All,

i have a very peculiar problem

i my work sheet i have a date time picker control. it works fine every thing is working fine but...............

each time i open my work book this date time picker control will be resized

(assume if the original height = 17.25 and width = 139.5 it is be changed to height = 20 and width = 50)

if i move horizontal or vertical scroll bars the sizes get reseted to original size.
this is making my work sheet look very ugly at the time of opening the file.

Plz Plz Plz help me out to solve this probelm.
1. How to make my date time picker size unchanged during opening of the file.

I need some help getting a Date & Time stamp in a range E3:E300 whenever there is a change to a cell to that range.

example of output:
09-26-08 02:00 PM - "example of change made"

I placed a Date Time Picker control on a spreadsheet, resized it, linked it,
etc. and closed the spreadsheeet. Now evertime you open the spreadsheet the
control height opens up to about 5 cells. Is there anyway to prevent this?

If anyone can answer this question .. YAY.
I have two columns within a spreadsheet. I want a date & time value in each column depending on when the field was entered.

My problem is using NOW() .. this updates to the current time and date for all fields within the column.

Is there anyway to add a date & time within a field and lock the values once they are entered?

Ultimately, I would like to calculate the exact time between the two fields.

HELP!

hi all,

is there a way to use wrokday function in my worksheet controls.?

i have a date time picker in my worksheet and a text box and a button.

on click of this button i should get next desired date.

like when i select a date from the calendar and click on button there
is onclick function in my macro.

this onclick should calculate next desired date(assume if i choose
10/11/2005 and add 2 to this date i should get 12/11/2005)

i know how to use workday function using a cell reference.
but................
i don't know how to put this into a vba code.
if i use workday("10/11/2005", 2) i get sub or function not found error
message.

Please Please help me out to solve this problem.

--
sjayar
------------------------------------------------------------------------
sjayar's Profile: http://www.excelforum.com/member.php...o&userid=28435
View this thread: http://www.excelforum.com/showthread...hreadid=483863

Hello!
I am working on a file, that has data in column "E" that starts with G and then a number from 1 to 9, and then some more numbers. Dates are saved in column "I". Checkboxes would be G1, G2, ... G9 and UseTimeInverval. There would also be two date time pickers. If I selected UseTimeInterval, it would filter depending on all dates between selected dates, and all checked values in checkboxes. If UseTimeInterval would not be selected, then it would just filter using values from G1, G2 and so on.
If lets say only G1 is selected, it has to return all values that start with G1, and if G1-G9 are all selected, it has to return all data.
Is there a simple way to make this, because the only solution I can think of is with like 100 IF functions?

Any help will be greatly appreciated, and all questions will be gladly answered.

I've got a spreadsheet that I am interested in calculating difference between times that span over two days. I'm trying to eliminate the amount of keystrokes and ensure data integrity so I am wanting to use the date and time picker in excel.

I've created a userform called userform1 placed a custom formatted date time picker control that I formatted it so that it shows date MM/dd/yyyy and time H:mm.

I need some help getting the selected date and time into the active cell, preferrably upon closing the userform.
I found some vba on ozgrid for the calendar control, but it doesn't seem to translate to the date/time picker.

In the userform I've inserted this code and when I call the userform via macro, I get this error "method or data member not found" and the debugger highlights Me.Calendar1 in the Userform_Activate procedure

Private Sub
Calendar1_Click()
ActiveCell = Calendar1.Value
    ActiveCell.NumberFormat = "mm/dd/yy"
End Sub
    
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub
Can someone help me mod this or provide some alternate code?

Thanks!


No luck finding an answer? You could always try Google.