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

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

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

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


.

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


.