DateTime picker control
 

In my Excel project, I like to provide this control so that user can choose
hrs and min to transfer to worksheet or a form.

how do I get this control and use it. i these discussions it is possible
and people are using in VBA.

your help, suggestions or sample code will help.

Thank you
MVM

DateTime picker control
 

Hi MVM

See
http://msdn.microsoft.com/library/de...me/reflist.asp

Set Custom to 3 - dtpCustom and CustomFormat to h:mm to display time
And Updown to True in the properties





--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
In my Excel project, I like to provide this control so that user can choose
hrs and min to transfer to worksheet or a form.

how do I get this control and use it. i these discussions it is possible
and people are using in VBA.

your help, suggestions or sample code will help.

Thank you
MVM

DateTime picker control
 

I believe that the Date and Time Picker comes with Visual Studio, and not MS
Office. The calendar control that comes with office is the Calendar Control
11.0(?), and that is inserted into a workbook from the Control ToolBox
Toolbar.
--
Kevin Backmann


"MVM" wrote:

In my Excel project, I like to provide this control so that user can choose
hrs and min to transfer to worksheet or a form.

how do I get this control and use it. i these discussions it is possible
and people are using in VBA.

your help, suggestions or sample code will help.

Thank you
MVM

DateTime picker control
 

Ron:
Thanks a lot. That works.
MVM

"Ron de Bruin" wrote:

Hi MVM

See
http://msdn.microsoft.com/library/de...me/reflist.asp

Set Custom to 3 - dtpCustom and CustomFormat to h:mm to display time
And Updown to True in the properties





--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
In my Excel project, I like to provide this control so that user can choose
hrs and min to transfer to worksheet or a form.

how do I get this control and use it. i these discussions it is possible
and people are using in VBA.

your help, suggestions or sample code will help.

Thank you
MVM

DateTime picker control
 

I got the DTPicker control and did some programming.

I placed one in each of the two Spreadsheets and one on a form.
when i invoke the picker, it display the control at some position. When I
click on a different cell, it is supposted to be around the cell. It does
that (call it pk-1). But also opens another copy at the location when it
first came in (pk-2). Only this one is real. otherone is not real. with
pk-1 i just can't do any thing. when I click on pk-2, pk-1 disappears.

I used LostFocus to move the date and time to the cells. What i noticed is,
when I invoke the control, it won't get the focus. I tried Activate etc.
How do I high light the hours or minutes as soon as it appears. This also
solves the above problem. If I can get the focus for pk-2 programmatically,
pk-1 will disappear.

Any suggestions - highly appreciated.

Also where Can i find the discriptions of the picker fields. In properties
box I see position, hour and minute. But in Object Browser I don't see these.

Thanks
MVM

"Ron de Bruin" wrote:

Hi MVM

See
http://msdn.microsoft.com/library/de...me/reflist.asp

Set Custom to 3 - dtpCustom and CustomFormat to h:mm to display time
And Updown to True in the properties





--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
In my Excel project, I like to provide this control so that user can choose
hrs and min to transfer to worksheet or a form.

how do I get this control and use it. i these discussions it is possible
and people are using in VBA.

your help, suggestions or sample code will help.

Thank you
MVM

DateTime picker control
 

One more observation:
When I go on debug mode - it perfectly places the control. I don't see two
images. I am viewing the excel sheet and the codesheet simultaneously. But
if I run without the debug mode I see two controls. one at the original
position and it is active and the other at the place I want (top and left are
changed based on the target cell). This is not active and disappears as soon
as i click on the first one.
Thanks
MVM

"MVM" wrote:

I got the DTPicker control and did some programming.

I placed one in each of the two Spreadsheets and one on a form.
when i invoke the picker, it display the control at some position. When I
click on a different cell, it is supposted to be around the cell. It does
that (call it pk-1). But also opens another copy at the location when it
first came in (pk-2). Only this one is real. otherone is not real. with
pk-1 i just can't do any thing. when I click on pk-2, pk-1 disappears.

I used LostFocus to move the date and time to the cells. What i noticed is,
when I invoke the control, it won't get the focus. I tried Activate etc.
How do I high light the hours or minutes as soon as it appears. This also
solves the above problem. If I can get the focus for pk-2 programmatically,
pk-1 will disappear.

Any suggestions - highly appreciated.

Also where Can i find the discriptions of the picker fields. In properties
box I see position, hour and minute. But in Object Browser I don't see these.

Thanks
MVM

"Ron de Bruin" wrote:

Hi MVM

See
http://msdn.microsoft.com/library/de...me/reflist.asp

Set Custom to 3 - dtpCustom and CustomFormat to h:mm to display time
And Updown to True in the properties





--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
In my Excel project, I like to provide this control so that user can choose
hrs and min to transfer to worksheet or a form.

how do I get this control and use it. i these discussions it is possible
and people are using in VBA.

your help, suggestions or sample code will help.

Thank you
MVM

DateTime picker control
 

Hi MVM

Is your zoom 100% ?

Bed time here so I reply tomorrow

--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
One more observation:
When I go on debug mode - it perfectly places the control. I don't see two
images. I am viewing the excel sheet and the codesheet simultaneously. But
if I run without the debug mode I see two controls. one at the original
position and it is active and the other at the place I want (top and left are
changed based on the target cell). This is not active and disappears as soon
as i click on the first one.
Thanks
MVM

"MVM" wrote:

I got the DTPicker control and did some programming.

I placed one in each of the two Spreadsheets and one on a form.
when i invoke the picker, it display the control at some position. When I
click on a different cell, it is supposted to be around the cell. It does
that (call it pk-1). But also opens another copy at the location when it
first came in (pk-2). Only this one is real. otherone is not real. with
pk-1 i just can't do any thing. when I click on pk-2, pk-1 disappears.

I used LostFocus to move the date and time to the cells. What i noticed is,
when I invoke the control, it won't get the focus. I tried Activate etc.
How do I high light the hours or minutes as soon as it appears. This also
solves the above problem. If I can get the focus for pk-2 programmatically,
pk-1 will disappear.

Any suggestions - highly appreciated.

Also where Can i find the discriptions of the picker fields. In properties
box I see position, hour and minute. But in Object Browser I don't see these.

Thanks
MVM

"Ron de Bruin" wrote:

Hi MVM

See
http://msdn.microsoft.com/library/de...me/reflist.asp

Set Custom to 3 - dtpCustom and CustomFormat to h:mm to display time
And Updown to True in the properties





--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
In my Excel project, I like to provide this control so that user can choose
hrs and min to transfer to worksheet or a form.

how do I get this control and use it. i these discussions it is possible
and people are using in VBA.

your help, suggestions or sample code will help.

Thank you
MVM

DateTime picker control
 

yes my excel is 100% zoomed.

thank you
MVM

"Ron de Bruin" wrote:

Hi MVM

Is your zoom 100% ?

Bed time here so I reply tomorrow

--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
One more observation:
When I go on debug mode - it perfectly places the control. I don't see two
images. I am viewing the excel sheet and the codesheet simultaneously. But
if I run without the debug mode I see two controls. one at the original
position and it is active and the other at the place I want (top and left are
changed based on the target cell). This is not active and disappears as soon
as i click on the first one.
Thanks
MVM

"MVM" wrote:

I got the DTPicker control and did some programming.

I placed one in each of the two Spreadsheets and one on a form.
when i invoke the picker, it display the control at some position. When I
click on a different cell, it is supposted to be around the cell. It does
that (call it pk-1). But also opens another copy at the location when it
first came in (pk-2). Only this one is real. otherone is not real. with
pk-1 i just can't do any thing. when I click on pk-2, pk-1 disappears.

I used LostFocus to move the date and time to the cells. What i noticed is,
when I invoke the control, it won't get the focus. I tried Activate etc.
How do I high light the hours or minutes as soon as it appears. This also
solves the above problem. If I can get the focus for pk-2 programmatically,
pk-1 will disappear.

Any suggestions - highly appreciated.

Also where Can i find the discriptions of the picker fields. In properties
box I see position, hour and minute. But in Object Browser I don't see these.

Thanks
MVM

"Ron de Bruin" wrote:

Hi MVM

See
http://msdn.microsoft.com/library/de...me/reflist.asp

Set Custom to 3 - dtpCustom and CustomFormat to h:mm to display time
And Updown to True in the properties





--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
In my Excel project, I like to provide this control so that user can choose
hrs and min to transfer to worksheet or a form.

how do I get this control and use it. i these discussions it is possible
and people are using in VBA.

your help, suggestions or sample code will help.

Thank you
MVM

DateTime picker control
 

Hi
I have the same problem: this control works OK on a user form but is going
completly crazy on a worksheet!
If you can solve the problem I'm deeply interested.

Zorro

DateTime picker control
 

Hi

A very quick look at it :

It seems that if you use a linked cell in the properties
it is working (no double controls)

But it is very buggy this control
Why not use two comboboxes on the worksheet ?

Or for dates see
http://www.rondebruin.nl/calendar.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Zorro" wrote in message ...
Hi
I have the same problem: this control works OK on a user form but is going completly crazy on a worksheet!
If you can solve the problem I'm deeply interested.

Zorro

DateTime picker control
 

Thank you Ron
As aways you showed us a way.
Can you let MS know about this. It is strange that it works with linked cell.

MVM

"Ron de Bruin" wrote:

Hi

A very quick look at it :

It seems that if you use a linked cell in the properties
it is working (no double controls)

But it is very buggy this control
Why not use two comboboxes on the worksheet ?

Or for dates see
http://www.rondebruin.nl/calendar.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Zorro" wrote in message ...
Hi
I have the same problem: this control works OK on a user form but is going completly crazy on a worksheet!
If you can solve the problem I'm deeply interested.

Zorro

DateTime picker control
 

Hi MVM

Can you let MS know about this

I do that



--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
Thank you Ron
As aways you showed us a way.
Can you let MS know about this. It is strange that it works with linked cell.

MVM

"Ron de Bruin" wrote:

Hi

A very quick look at it :

It seems that if you use a linked cell in the properties
it is working (no double controls)

But it is very buggy this control
Why not use two comboboxes on the worksheet ?

Or for dates see
http://www.rondebruin.nl/calendar.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Zorro" wrote in message ...
Hi
I have the same problem: this control works OK on a user form but is going completly crazy on a worksheet!
If you can solve the problem I'm deeply interested.

Zorro

DateTime picker control
 

On my computer I could locate DTPicker and reference it. After reading some
questions on this, I checked with 3 other computers who don't have vs.net or
vb. I found there too.
I spent lot of time and got the DTPicker working with your help. I now
found this is not available on one of our employee's computer. I am very
confused with which package it is coming.

by the by ...
when I define the visibility = true in the same function where I am
redefining the position, it works consistently.

Thanks for all the help.
MVM

"Ron de Bruin" wrote:

Hi MVM

Can you let MS know about this

I do that



--
Regards Ron De Bruin
http://www.rondebruin.nl



"MVM" wrote in message ...
Thank you Ron
As aways you showed us a way.
Can you let MS know about this. It is strange that it works with linked cell.

MVM

"Ron de Bruin" wrote:

Hi

A very quick look at it :

It seems that if you use a linked cell in the properties
it is working (no double controls)

But it is very buggy this control
Why not use two comboboxes on the worksheet ?

Or for dates see
http://www.rondebruin.nl/calendar.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Zorro" wrote in message ...
Hi
I have the same problem: this control works OK on a user form but is going completly crazy on a worksheet!
If you can solve the problem I'm deeply interested.

Zorro

DateTime picker control
 

DTPicker definitely has problems when embedded in an Excel sheet. The
only thing I have found is to include a macro in the workbook Open
event.

Option Explicit

Private Sub Workbook_Open()
Dim strTemp As String
Dim dtpMy As New DTPicker
Dim objMy As OLEObject
Dim sheMy As Worksheet
Dim winMy As Window

' force the date time picker to correct itself.
For Each sheMy In Me.Worksheets ' Search all sheets
For Each objMy In sheMy.OLEObjects ' Search OLEObjects for
date time pickers
If TypeName(objMy.Object) = "DTPicker" Then
If sheMy.Name = ActiveSheet.Name Then ' This trick only
works for active sheets
objMy.Top = objMy.Top + 0.01
objMy.Top = objMy.Top - 0.01
Else
If strTemp <= "" Then strTemp = ActiveSheet.Name
sheMy.Activate
objMy.Top = objMy.Top + 0.01
objMy.Top = objMy.Top - 0.01
End If
DoEvents
Exit For
End If
Next ' OLEObject loop
Next ' Sheet loop
If strTemp "" Then Me.Worksheets(strTemp).Activate
Me.Saved = True
End Sub




*** Sent via Developersdex http://www.developersdex.com ***

DateTime picker control
 

Try wiggling the control when you open the workbook. Use this macro in
the workbook open:

Private Sub Workbook_Open()
Dim strTemp As String, nbrTop As Double
Dim dtpMy As New DTPicker
Dim objMy As OLEObject
Dim sheMy As Worksheet
Dim winMy As Window

' force the date time picker to correct itself.
For Each sheMy In Me.Worksheets ' Search all sheets
For Each objMy In sheMy.OLEObjects ' Search OLEObjects for
date time pickers
If TypeName(objMy.Object) = "DTPicker" Then
If sheMy.Name = ActiveSheet.Name Then ' This trick only
works for active sheets
'
Else
If strTemp <= "" Then strTemp = ActiveSheet.Name
sheMy.Activate
nbrTop = objMy.Top
End If
nbrTop = objMy.Top
objMy.Top = nbrTop + 1

ActiveWindow.ScrollRow = ActiveWindow.ScrollRow + 1
DoEvents
objMy.Top = nbrTop
ActiveWindow.ScrollRow = ActiveWindow.ScrollRow - 1
DoEvents
Exit For
End If
Next ' OLEObject loop
Next ' Sheet loop
If strTemp "" Then Me.Worksheets(strTemp).Activate
Me.Saved = True
End Sub







*** Sent via Developersdex http://www.developersdex.com ***