Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calender Dropdown with TIME (EXCEL 97/2000)

    I want to allow users to quickly select a date & time.
    I have been playing with DTPicker.OCX which allows the well know calendar drop-down to be displayed and lets the user easily select a date. The problem is that I want them to select a date & a time.
    Now I've seen that it is possible to include a time as well but this needs to be typed and/or set using up/down arrow keys. I would have liked a combined date/time graphical 'thing'.

    Anyone know if something like this is 'out there' ??

    Erik Jan

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calender Dropdown with TIME (EXCEL 97/2000)

    Erik,

    I don't know of any control that combines both a calendar and time. In the past I have used the spin button control to change the value of a textbox.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calender Dropdown with TIME (EXCEL 97/2000)

    OK, getting date AND time in one nicely designed drop-down type control is not possible (now)...

    So, now I want to use the calender-dropdown (DTPICKER - nice date selection only) on my sheet.
    "Pulled" the control and placed it in a cell.... What I really want is that ONLY the little dropdown-arrow is visible and NOT the content (that shows in a linked-cell elsewhere) the original cell underneath should display!
    So, e.g. like a validation, you click the down-arrow and THEN you get to see & use the nice calender-view.
    I tried to e.g. make the defualt-visible part of the control white (so it won't show up) but this doesn't seem to work. Are there ways to do this or will I require VBA-code to create these 'on-the-fly'???

    Any help & suggestions and appreciated!

    Thanks,

    Erik Jan

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calender Dropdown with TIME (EXCEL 97/2000)

    Erik,

    You can go into design mode and make the size of the control such that only the drop down arrow shows. Drag the left side of the control to the right to show as much or as little of the control as you wish. Is this what you mean?

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calender Dropdown with TIME (EXCEL 97/2000)

    Yep... that's good enough... clean & simple... thanks.

    Next I found out that this think is not supported in EX97 (I develop in 2000 but some users have 97). My idea is to use Application.Version to find out if this is EX2000 running and then add the DTpicker control. I recorded the call to add but cannot:
    1. find ways to change the properties (I want custom-format dd-mmm-yyyy), font-size etc.
    2. size it so it gets right aligned in my cell and as wide as the drop-down arrow is. For that I need the cell sizes and the width of the dropdown arrow...

    Esp. the second point seems difficult, if I know how to change one property, I can handle the first point from there.

    Appreciate your comments!

    Erik Jan

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calender Dropdown with TIME (EXCEL 97/2000)

    I tried some things and came up with this:

    Sub Macro1()
    Dim oCalender As Object

    Set oCalender = ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calen dar.7", Link:=False, _
    DisplayAsIcon:=False, Left:=158.4, Top:=41.4, Width:=158.4, Height:= _
    82.8)


    With oCalender.Object
    .MonthLength = 0
    .BackColor = RGB(200, 200, 200)
    End With
    With oCalender
    .Top = ActiveSheet.Cells(5, 3).Top
    .Left = ActiveSheet.Cells(5, 3).Left
    .Width = 100
    .Height = 50
    End With
    End Sub

    If you need the other properties, got to the object browser (in the VBE) and search for Calender. Highlight the one you need and press F1 for help.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calender Dropdown with TIME (EXCEL 97/2000)

    Jan Karel,

    Yeah... makes sense. I use the DTPIcker-control however. Here's what I made of it:

    Sub Macro1()
    Dim oDTP As Object

    Set oDTP = ActiveSheet.OLEObjects.Add(ClassType:="MSComCtl2.D TPicker.2", Link:=False, _
    DisplayAsIcon:=False, Left:=237, Top:=56.25, Width:=118.5, Height:=33)

    With oDTP
    .Top = ActiveSheet.Cells(5, 3).Top
    .Left = ActiveSheet.Cells(5, 3).Left
    .Width = 100
    .Height = 50
    End With
    End Sub

    If I put a break (F9) on the "With oDTP" line to check controls, codes and play in the immediate-mode pane, I get "Can't enter Break-mode now"...
    Also, adding the object will also add a reference to it in the Tools reference-list. If next I load in EXCEL97, I get an error in my code as the OCX is missing on that (other) system. I cannot remove it there (even not by hand...) as it says "IN USE" even though it lists as "MISSING"....
    Anyway, I think I'll give up on this one...

    Thanks for your help & suggestions...

    Erik Jan <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calender Dropdown with TIME (EXCEL 97/2000)

    The only way I can uncheck the reference is by deleting the sheet the control was once added to (even if it is deleted, XL somehow remembers). Not very neat.
    Of course you could try copying (and registering) the OCX on the other system.

    Lastly, if you need to access the methods and properties of the control itself (not the placement and size of the control, those are accessible through oDTP.WhateverProperty), use oDTP.Object.ProperyOrMethod

    I too could not enter break mode.

    So what about using data, validation, List and have a list with times with -say- an interval of 15 minutes?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calender Dropdown with TIME (EXCEL 97/2000)

    Erik,

    Not sure at the moment about the second step, but you can change the format by something similar to the following:

    <pre>DTPicker1.CustomFormat = "mm:dd:yy"
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •