Timestamping

  • Thread starter Thread starter ktoth04
  • Start date Start date
K

ktoth04

Hi, I would like to timestamp my document as follows
In one instance, I would like to insert a timestamp in M# whenever it moves
from all the cells in that row being blank to all but one of them being blank
(first instance of a record in this row). I would also like to remove the
timestamp if everything in the row besides M# again becomes blank.

In the 2nd instance, I would like to update N# whenever anything in the row
is updated, with a current timestamp. (ie, last edited date)

Any help?
 
This will put a timestamp in column L whenever a cell in the same row
is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Count > 1 Then
Exit Sub
Else
If Right(Target.Address, 2) = "$1" Then Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With Me
If Left(Target.Address, 3) = "$A$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$B$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$C$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$D$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$G$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$H$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
End If
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Exit Sub
errHandler:
MsgBox Err.Number & " " & Err.Description
Application.EnableEvents = True
End Sub

Cliff Edwards
 
Hi, I would like to timestamp my document as follows
In one instance, I would like to insert a timestamp in M# whenever it moves
from all the cells in that row being blank to all but one of them being blank
(first instance of a record in this row). I would also like to remove the
timestamp if everything in the row besides M# again becomes blank.

In the 2nd instance, I would like to update N# whenever anything in the row
is updated, with a current timestamp. (ie, last edited date)

Any help?
Click to expand...

Try looking for info on the countif() function to determine if there
are any non-blank cells.

I'd use the worksheet event: worksheet_changed(byval...)
then put an If statement in the event sub:
If target.column = # then

hope this gives you a good direction!
Cheers!
Nate
 
Thank you very much! If I have values in columns other than A-H, can I just
copy and paste, and substitute the other columns?
 
Try this worksheet event code:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
tr = t.Row
Set r = Range("A:L")
If Intersect(t, r) Is Nothing Then Exit Sub
Set rr = Range("A" & tr & ":L" & tr)
n = Application.WorksheetFunction.CountA(rr)
Application.EnableEvents = False

If n = 0 Then
Cells(tr, "M").Clear
End If
If n = 1 Then
Cells(tr, "M").Value = Now
End If

Cells(tr, "N") = Now
Application.EnableEvents = True
End Sub
 
Sorry, I copied this out of an existing project - it only time-stamps
when cells in columns a,b,c,d,g or h are changed, but you get the
idea.

Cliff Edwards
 
Thank you very much!  If I have values in columns other than A-H, can I just
copy and paste, and substitute the other columns?
Click to expand...

Yes, you can copy/paste and change column references. If you get a
chance, try using Gary's Students solution which also clears the
timestamp.

I'll probably update my project to something like that.

Cliff Edwards
 
Back
Top