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

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" <[email protected]> wrote in message news:[email protected]...
> 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

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

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" <[email protected]> wrote in message news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> > > 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
> >
> >
> >

Hi MVM

Is your zoom 100% ?

Bed time here so I reply tomorrow

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



"MVM" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > > 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
>> >
>> >
>> >

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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> > > 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
> >> >
> >> >
> >> >
>
>
>

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

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" <[email protected]> wrote in message news:[email protected]...
> 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
>

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" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
>
>
>

Hi MVM

> Can you let MS know about this

I do that



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



"MVM" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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
>> >
>>
>>
>>

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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>

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 ***

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 ***