Repost: Calendar Control: Ron De Bruin

  • Thread starter Thread starter Michael
  • Start date Start date
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
 
Michael,

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

John
 
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
 
Tim,

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

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

Tim
 
Back
Top