Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date & Time Picker Control 6.0 - another challenge (for me)
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 ***************** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date & Time Picker Control 6.0 - another challenge (for me)
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 ***************** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date & Time Picker Control 6.0 - another challenge (for me)
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" wrote: 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 ***************** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date & Time Picker Control 6.0 - another challenge (for me)
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. "Michael R" wrote: 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" wrote: 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 ***************** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date & Time Picker Control 6.0 - another challenge (for me)
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 ***************** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time Picker Control 6.0 | Excel Programming | |||
date and time picker control | Excel Discussion (Misc queries) | |||
date time picker control | Excel Programming | |||
Problem With Date and Time Picker Control 6.0 | Excel Discussion (Misc queries) | |||
Date and Time Picker Control | Excel Discussion (Misc queries) |