Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Create new Date Time Picker on active cell if date format, delete on selection change

  1. #1
    Join Date
    1st March 2006
    Posts
    2

    Create new Date Time Picker on active cell if date format, delete on selection change

    Current attempt:
    VB:
    Public Sub ShowDTPicker() 
        Dim dtp 
        If IsDate(ActiveCell) Then 
            Set dtp = ActiveSheet.OLEObjects.Add("MSComCtl2.DTPicker.2") 
            With dtp 
                .Name = "dtp" 
                .Top = ActiveCell.Top 
                .Left = ActiveCell.Left 
                .Width = ActiveCell.Width 
                .Height = ActiveCell.Height 
                .LinkedCell = ActiveCell.Address 
            End With 
        Else 
            On Error Resume Next 
            ActiveSheet.OLEObjects("dtp").Delete 
        End If 
    End Sub 
    
    
    The control is created & deleted however... the picker drop-down will not work.
    Stranger still: after the DTP is created, if i toggle to design mode & manually change any property of the DTP then toggle out of design mode - it works fine.
    Strange x 2: If i add a "global" watch for ActiveSheet.OLEObjects(1) after the control is created then i need to do the "Stranger still" procedure twice before the DTP will drop down properly.

    My guess was that this has something to do with some kinda "namespace / handle / pointer" holding on to the variable until the manual changes release it. (The random stabs at proper vernacular may have tipped you off that this is beyond my domain. ) i tried setting dtp=nothing, but that did nothing (predictably?)

    Thanks in advance for any advice!
    matt

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    Re: Create new Date Time Picker on active cell if date format, delete on selection change

    Hi Matt

    Welcome to ozgrid

    Strange x 2: If i add a "global" watch for ActiveSheet.OLEObjects(1) after the control is created then i need to do the "Stranger still" procedure twice before the DTP will drop down properly.
    What's the "Stranger still" procedure?

    Have you tried using the Calendar control instead?

  3. #3
    Join Date
    1st March 2006
    Posts
    2

    Re: Create new Date Time Picker on active cell if date format, delete on selection ch

    Stranger still: after the DTP is created, if i toggle to design mode & manually change any property of the DTP then toggle out of design mode - it works fine.
    A correction however: changing any property does not "fix" the problem... only changing height, width, Locked.

    I just tried the calendar control... didn't try it originally because it's not quite the behaviour / appearance i wanted.

    So now i tried it... and its got the same hang-up as the DTP. After the vba creates it, it will not respond to clicks at all; but if i manually toggle Design mode On then Off... it works fine thereafter!?!? (don't even need to change a property with this control.)

    just plain weird... is there an obvious mistake with my code?
    this should work as-is, right?

    BTW: the ShowDTPicker sub is called by Workbook_SheetSelectionChange
    Would that cause the glitch?
    Last edited by MattSim; March 3rd, 2006 at 03:39.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Date And Time Picker Control In 2007
    By JOR in forum EXCEL HELP
    Replies: 6
    Last Post: August 29th, 2007, 22:38
  2. Date/time Picker Control
    By abbeville in forum EXCEL HELP
    Replies: 2
    Last Post: October 15th, 2006, 18:47
  3. date and time picker
    By JOHNT in forum EXCEL HELP
    Replies: 2
    Last Post: May 15th, 2006, 04:39
  4. date and time picker
    By jcastrejon1 in forum EXCEL HELP
    Replies: 5
    Last Post: March 14th, 2006, 00:08
  5. [Solved] VBA: Date and time picker?
    By Raydreamk in forum EXCEL HELP
    Replies: 9
    Last Post: August 2nd, 2003, 00:37

Bookmarks

Posting Permissions

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