Code Bug
 

Hi...

I'm trying to time stamp a key stroke...when a user keys a
Y I want a cell 3 to the left top enter now(). I think the
code is perfect but it just won't work...Can anyone do
this better...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("AD8:$AD700")) Is Nothing
Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "Y" Then
Target.Offset(0, 3).Value = Now()
Target.Offset(0, 3).NumberFormat = "dd/mm"
Target.EntireColumn.AutoFit
End If
End If
End If

Cheers

GC

Code Bug
 

Jack,

What is going wrong? Is it going 3 right instead of 3 up? If so, change
Offset(0, 3) to Offset(-3,0). If not, enlighten us.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jack Foster" wrote in message
...
Hi...

I'm trying to time stamp a key stroke...when a user keys a
Y I want a cell 3 to the left top enter now(). I think the
code is perfect but it just won't work...Can anyone do
this better...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("AD8:$AD700")) Is Nothing
Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "Y" Then
Target.Offset(0, 3).Value = Now()
Target.Offset(0, 3).NumberFormat = "dd/mm"
Target.EntireColumn.AutoFit
End If
End If
End If

Cheers

GC

Code Bug
 

It seems to work for me. One thing you may want to do when you'r
changing a cells value using the Worksheet_Change event is to tur
events off before changing the cell and turn it back on afterwards:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A8:$A700")) Is Nothing Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "Y" Then
Application.EnableEvents = False
Target.Offset(0, 3).Value = Now()
Target.Offset(0, 3).NumberFormat = "dd/mm"
Target.EntireColumn.AutoFit
Application.EnableEvents = True
End If
End If
End If
End Su

--
Message posted from http://www.ExcelForum.com

Code Bug
 

You might also want to change
Target.Offset(0, 3).Value = Now()
to
Target.Offset(0, 3).Value = Date

--
Don Guillett
SalesAid Software

"Bob Phillips" wrote in message
...
Jack,

What is going wrong? Is it going 3 right instead of 3 up? If so, change
Offset(0, 3) to Offset(-3,0). If not, enlighten us.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jack Foster" wrote in message
...
Hi...

I'm trying to time stamp a key stroke...when a user keys a
Y I want a cell 3 to the left top enter now(). I think the
code is perfect but it just won't work...Can anyone do
this better...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("AD8:$AD700")) Is Nothing
Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "Y" Then
Target.Offset(0, 3).Value = Now()
Target.Offset(0, 3).NumberFormat = "dd/mm"
Target.EntireColumn.AutoFit
End If
End If
End If

Cheers

GC

Code Bug
 

I meant to say TIME instead of Date

--
Don Guillett
SalesAid Software

"Bob Phillips" wrote in message
...
Jack,

What is going wrong? Is it going 3 right instead of 3 up? If so, change
Offset(0, 3) to Offset(-3,0). If not, enlighten us.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jack Foster" wrote in message
...
Hi...

I'm trying to time stamp a key stroke...when a user keys a
Y I want a cell 3 to the left top enter now(). I think the
code is perfect but it just won't work...Can anyone do
this better...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("AD8:$AD700")) Is Nothing
Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "Y" Then
Target.Offset(0, 3).Value = Now()
Target.Offset(0, 3).NumberFormat = "dd/mm"
Target.EntireColumn.AutoFit
End If
End If
End If

Cheers

GC