Data Validation Won't Activate Worksheet_Change(ByVal Target As Range)


Posted by Sid on August 03, 2001 1:20 AM

I am using the following to insert the date next to a cell when that cell has an input ( from Ivan I think, apologies if not)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keyRange As Range
Set keyRange = Range("j3:j65536", "l3:l65336")
Application.EnableEvents = False
If Not Intersect(keyRange, Target) Is Nothing Then
If Target <> "" Then
Target.Offset(0, 1) = Date
Else
Target.Offset(0, 1) = ""
End If
End If
Application.EnableEvents = True
End Sub
This works fine until I put a data validation list on the target cell - Excel 97 just does'nt see a change to the cell. ( Excel 2000 does, but I need it for '97)

Can anyone help
Thanx Sid



Posted by Ivan F Moala on August 03, 2001 3:01 AM

place a formula in any cell that references the
data validation cell eg =j3 in say z1.....


ivan