Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Bug
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |