TIME STAMP FOR A RANGE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FARAZ QURESHI - 27 Mar 2008 09:51 GMT

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

Signature

Best Regards,
FARAZ A. QURESHI

Reply to this Message

Mike H - 27 Mar 2008 10:06 GMT

Hi,

Deleting this line should do it
If .Count > 1 Then Exit Sub

Mike

> I found the following code at http://www.mcgimpsey.com/excel/timestamp.html
> quite useful.
[quoted text clipped - 23 lines]
> End With
> End Sub

Reply to this Message

Gary''s Student - 27 Mar 2008 10:09 GMT

Just remove this line:

If .Count > 1 Then Exit Sub

Signature

Gary''s Student - gsnu200775

Reply to this Message

Dave Peterson - 27 Mar 2008 12:19 GMT

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

> I found the following code at http://www.mcgimpsey.com/excel/timestamp.html
> quite useful.
[quoted text clipped - 28 lines]
> Best Regards,
> FARAZ A. QURESHI

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 27 Mar 2008 14:22 GMT

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

> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
[quoted text clipped - 58 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message