Repost: Calendar Control: Ron De Bruin

M

Michael

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
 
J

John Wilson

Michael,

Try replacing:
If Target.Column = 4 Then
with:
If Target.Address = Range("D4").Address Then

John
 
T

Tim Zych

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
 
J

John Wilson

Tim,

From the immediate window...
Range("D4").Address does come up with $D$4
Using Excel 2000

John
 
T

Tim Zych

You're right. I forgot about the default of the Address
property. I'd still stick with Intersect for the reason
noted.

Tim
 
Top