Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with timestamp macro
I'm currently using this macro to create a timestamp in excel:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("J:K"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub problem is the date keeps on popping up to the right of the edited cell. I would like to be able to monitor two columns ie, J:K, and the timestamp to show up in the same row but in column L. Is there a way to do it? I'm really preetty new at VBA and any help would be much appreciated. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with timestamp macro
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target Set r = Cells(Target.Row, "L") If .Count 1 Then Exit Sub If Not Intersect(Range("J:K"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then r.ClearContents Else With r .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub -- Gary''s Student - gsnu200801 "jrm" wrote: I'm currently using this macro to create a timestamp in excel: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("J:K"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub problem is the date keeps on popping up to the right of the edited cell. I would like to be able to monitor two columns ie, J:K, and the timestamp to show up in the same row but in column L. Is there a way to do it? I'm really preetty new at VBA and any help would be much appreciated. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with timestamp macro
This is great! thanks!
"Gary''s Student" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target Set r = Cells(Target.Row, "L") If .Count 1 Then Exit Sub If Not Intersect(Range("J:K"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then r.ClearContents Else With r .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub -- Gary''s Student - gsnu200801 "jrm" wrote: I'm currently using this macro to create a timestamp in excel: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("J:K"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub problem is the date keeps on popping up to the right of the edited cell. I would like to be able to monitor two columns ie, J:K, and the timestamp to show up in the same row but in column L. Is there a way to do it? I'm really preetty new at VBA and any help would be much appreciated. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timestamp ? | Excel Discussion (Misc queries) | |||
Timestamp | Excel Discussion (Misc queries) | |||
Timestamp | Excel Discussion (Misc queries) | |||
Now as timestamp | Excel Worksheet Functions | |||
Timestamp Macro | Excel Worksheet Functions |