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.
--
Rick (MVP - Excel)
"JAD" <(E-Mail Removed)> wrote in message
news:893C8E5B-C346-48E8-9598-(E-Mail Removed)...
>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
|
Similar Threads
