Date & Time Picker Control 6.0 - another challenge (for me)

[Michael R]

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 *****************

 

[Joel]

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]

We're one step further, but not there yet.

Now I get a "438 - Object doesn't support this property or method" on line
CurrentDTPickerCell.Value = .DTPicker1.Value

Of course I have adjusted the sheet name in the "With Sheets" statement

 

[Joel]

A global variable (not including class modules) doesn't retain its value.
When a macro ends so does the global variable life ends. It doesn't retain
the value. Also when you make a control invisible it doesn't mean that the
macro can't read the value of the control. Under these circumstances you
don't need the variable CurrentDTPickerCell unless you want to compare a
previous value with a new value.

If you need to compare the old and new values then you have to store the old
value some place in the workbook not in the macro or use class module
variables. Public Class varaibles due retain its value. Every time you
create a "new" instance of a class variable the value is retained.

 

[Rick Rothstein]

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.