Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Current attempt:
    Code:
    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

  • #2
    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?

    Comment


    • #3
      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, 03:39.

      Comment

      Working...
      X