Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Calendar Control: Ron De Bruin
Hi Ron, the code below you posted earlier works great for
a whole column. How do I isolate it to one cell only, say "d4", as I want the user only use the calender on one cell. Thanks, Michael Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 4 Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True Else: Calendar1.Visible = False End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Calendar Control: Ron De Bruin
Michael,
Try replacing: If Target.Column = 4 Then with: If Target.Address = Range("D4").Address Then John Michael wrote: Hi Ron, the code below you posted earlier works great for a whole column. How do I isolate it to one cell only, say "d4", as I want the user only use the calender on one cell. Thanks, Michael Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 4 Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True Else: Calendar1.Visible = False End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Calendar Control: Ron De Bruin
Hi John:
Excel likes to make Target absolute referenced. In this case Target.Address is" $D$4" and Range("D4").Address is "D4", so they will never be equal. One way around that is a slight modification: If Target.Address(0,0) = Range("D4").Address(0,0) Then or If Not Application.Intersect(Target, Target.Parent.Range("D4")) Is Nothing Then To make it more complicated, changing D4 will not be trapped by the .Address snippet if a value is pasted to multiple cells that include D4. If I copy a cell, select A1:D10, and paste, Target.Address will be "$A$1:$D$10", which of course doesn't equal "$D$4". So even though they intersect, the macro won't get triggered. I'd stick with .Intersect, although it ultimately depends on what the desired goal is. Best regards, Tim Zych "John Wilson" wrote in message ... Michael, Try replacing: If Target.Column = 4 Then with: If Target.Address = Range("D4").Address Then John Michael wrote: Hi Ron, the code below you posted earlier works great for a whole column. How do I isolate it to one cell only, say "d4", as I want the user only use the calender on one cell. Thanks, Michael Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 4 Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True Else: Calendar1.Visible = False End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Calendar Control: Ron De Bruin
You're right. I forgot about the default of the Address
property. I'd still stick with Intersect for the reason noted. Tim -----Original Message----- Tim, From the immediate window... Range("D4").Address does come up with $D$4 Using Excel 2000 John Tim Zych wrote: Hi John: Excel likes to make Target absolute referenced. In this case Target.Address is" $D$4" and Range ("D4").Address is "D4", so they will never be equal. One way around that is a slight modification: If Target.Address(0,0) = Range("D4").Address(0,0) Then or If Not Application.Intersect(Target, Target.Parent.Range ("D4")) Is Nothing Then To make it more complicated, changing D4 will not be trapped by the .Address snippet if a value is pasted to multiple cells that include D4. If I copy a cell, select A1:D10, and paste, Target.Address will be "$A$1:$D$10", which of course doesn't equal "$D$4". So even though they intersect, the macro won't get triggered. I'd stick with .Intersect, although it ultimately depends on what the desired goal is. Best regards, Tim Zych "John Wilson" wrote in message ... Michael, Try replacing: If Target.Column = 4 Then with: If Target.Address = Range("D4").Address Then John Michael wrote: Hi Ron, the code below you posted earlier works great for a whole column. How do I isolate it to one cell only, say "d4", as I want the user only use the calender on one cell. Thanks, Michael Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 4 Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True Else: Calendar1.Visible = False End If End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Calendar Control: Ron De Bruin
Tim,
You're right. That doesn't happen to me often. I'd still stick with Intersect for the reason noted. Agreed. Thanks, John Tim Zych wrote: You're right. I forgot about the default of the Address property. I'd still stick with Intersect for the reason noted. Tim -----Original Message----- Tim, From the immediate window... Range("D4").Address does come up with $D$4 Using Excel 2000 John Tim Zych wrote: Hi John: Excel likes to make Target absolute referenced. In this case Target.Address is" $D$4" and Range ("D4").Address is "D4", so they will never be equal. One way around that is a slight modification: If Target.Address(0,0) = Range("D4").Address(0,0) Then or If Not Application.Intersect(Target, Target.Parent.Range ("D4")) Is Nothing Then To make it more complicated, changing D4 will not be trapped by the .Address snippet if a value is pasted to multiple cells that include D4. If I copy a cell, select A1:D10, and paste, Target.Address will be "$A$1:$D$10", which of course doesn't equal "$D$4". So even though they intersect, the macro won't get triggered. I'd stick with .Intersect, although it ultimately depends on what the desired goal is. Best regards, Tim Zych "John Wilson" wrote in message ... Michael, Try replacing: If Target.Column = 4 Then with: If Target.Address = Range("D4").Address Then John Michael wrote: Hi Ron, the code below you posted earlier works great for a whole column. How do I isolate it to one cell only, say "d4", as I want the user only use the calender on one cell. Thanks, Michael Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 4 Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True Else: Calendar1.Visible = False End If End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calendar control | Excel Discussion (Misc queries) | |||
Calendar Control -- Is this possible? | Excel Discussion (Misc queries) | |||
Calendar Control | Excel Worksheet Functions | |||
calendar control 10.0 | Excel Discussion (Misc queries) | |||
Repost: Worksheet Change Method (Bob Philips, Ron De Bruin) | Excel Programming |