Date & Time Picker Control 6.0

[JAD]

I would like for the Date & Time Picker Control 6.0 to appear whenever a user
double clicks on a cell that requires a date. As an example, if the user is
in the worksheet named "WS1" and double clicks on cell "B11", the ActiveX
control will appear, allow you to select a date and then enter that date into
cell B11. Once entered, the Date & Time Picker Control 6.0 will disappear.
Any help would be appreciated. Thank You, JAD

 

[Joel]

Use a worksheet changge function which will make the control visible when you
need to make an entry and then make it invisible after the entry is made.

 

[Rick Rothstein]

Add the Date & Time Picker control to the worksheet's Control Toolbox (I am
not using a UserForm for this), then place it anywhere on worksheet WS1.
Then right click WS1's tab to go to the code window for that worksheet and
copy paste this code into that code window...

'***************** START OF CODE *****************
Dim CurrentDTPickerCell As Range

Private Sub DTPicker1_CloseUp()
CurrentDTPickerCell.Value = DTPicker1.Value
DTPicker1.Visible = False
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Not Intersect(Target, Range("A10,B11,C12")) Is Nothing Then
Cancel = True
Set CurrentDTPickerCell = Target
DTPicker1.Visible = True
DTPicker1.Top = Target.Top
DTPicker1.Left = Target.Left + Target.Width + 1
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A10,B11,C12")) Is Nothing Then
If DTPicker1.Visible Then DTPicker1.Visible = False
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If DTPicker1.Visible Then DTPicker1.Visible = False
End Sub
'***************** END OF CODE *****************

Simply change the range of values that this functionality should apply to in
both the BeforeDoubleClick and the Change events and you are done... go to
WK1 and start to use it. As set up, double clicking A10, B11 or C12 will
bring up the date picker control next to the cell allowing you to select a
date from there.