Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME STAMP FOR A RANGE
I found the following code at http://www.mcgimpsey.com/excel/timestamp.html
quite useful. The only question is that how to make it work if a series is entered if a collection/range is changed? e.g. 1. Selecting A2:A15, entering xyz and hitting Ctrl+Enter; OR 2. Copying xyz from some other place and pasting/filling all the range in a single stroke?: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A:A"), .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 -- Best Regards, FARAZ A. QURESHI |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME STAMP FOR A RANGE
Hi,
Deleting this line should do it If .Count 1 Then Exit Sub Mike "FARAZ QURESHI" wrote: I found the following code at http://www.mcgimpsey.com/excel/timestamp.html quite useful. The only question is that how to make it work if a series is entered if a collection/range is changed? e.g. 1. Selecting A2:A15, entering xyz and hitting Ctrl+Enter; OR 2. Copying xyz from some other place and pasting/filling all the range in a single stroke?: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A:A"), .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 -- Best Regards, FARAZ A. QURESHI |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME STAMP FOR A RANGE
Just remove this line:
If .Count 1 Then Exit Sub -- Gary''s Student - gsnu200775 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME STAMP FOR A RANGE
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myRng As Range With Target Set myRng = Intersect(.Cells, Me.Range("a2:a15")) If myRng Is Nothing Then Exit Sub End If For Each myCell In myRng.Cells 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 Next myCell End With End Sub FARAZ QURESHI wrote: I found the following code at http://www.mcgimpsey.com/excel/timestamp.html quite useful. The only question is that how to make it work if a series is entered if a collection/range is changed? e.g. 1. Selecting A2:A15, entering xyz and hitting Ctrl+Enter; OR 2. Copying xyz from some other place and pasting/filling all the range in a single stroke?: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A:A"), .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 -- Best Regards, FARAZ A. QURESHI -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME STAMP FOR A RANGE
I have a mistake in this code.
Use this instead: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myRng As Range Set myRng = Intersect(Target, Me.Range("a2:a15")) If myRng Is Nothing Then Exit Sub End If For Each myCell In myRng.Cells With myCell 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 With Next myCell End Sub Dave Peterson wrote: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myRng As Range With Target Set myRng = Intersect(.Cells, Me.Range("a2:a15")) If myRng Is Nothing Then Exit Sub End If For Each myCell In myRng.Cells 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 Next myCell End With End Sub FARAZ QURESHI wrote: I found the following code at http://www.mcgimpsey.com/excel/timestamp.html quite useful. The only question is that how to make it work if a series is entered if a collection/range is changed? e.g. 1. Selecting A2:A15, entering xyz and hitting Ctrl+Enter; OR 2. Copying xyz from some other place and pasting/filling all the range in a single stroke?: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A:A"), .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 -- Best Regards, FARAZ A. QURESHI -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Stamp | Excel Discussion (Misc queries) | |||
Time Stamp | Excel Worksheet Functions | |||
Time Stamp | Excel Discussion (Misc queries) | |||
time stamp | Excel Worksheet Functions | |||
Time Stamp | Excel Discussion (Misc queries) |