The control is associated with a particular worksheet. if another owrksheet
is visible you have to speify the sheet with the control or add the control
to each worksheet. Try this change (make the worksheet the sheet containing
the control).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheets("Sheet2")
CurrentDTPickerCell.Value = .DTPicker1.Value
.DTPicker1.Visible = False
End With
End Sub


"Michael R" wrote:

Using Excel2007
With Rick Rothstein's code (his post dd 09/10/2008 under "Date & Time Picker
Control 6.0" I managed to get the date picker to pop up on double click and
perform nicely as expected.

Now I wanted to provide the same functionality on a different sheet in the
same workbook. So I copied the code into this sheet but now I get a 424
"Object required" when I click (single or double) in any cell on that sheet;
the debugger stops at the line which I have marked with ==.
The other sheet is still working fine.

This is the code:
'***************** 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("B:C")) 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("B:C")) 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 *****************