I think you will have to place a second Date Picker control on the other
worksheet and duplicate my code there as well (making sure you change the
control name references in the second worksheet's code to the name you give
this second Date Picker control on that worksheet.

--
Rick (MVP - Excel)


"Michael R" wrote in message
...
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 *****************