Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamping
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamping
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamping
On Mar 3, 12:52 pm, ktoth04 wrote:
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? 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamping
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? "ward376" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamping
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 -- Gary''s Student - gsnu200771 "ktoth04" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamping
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamping
Yea, thanks very much!
"ward376" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamping
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? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TIMESTAMPING AMENDMENTS? | Excel Discussion (Misc queries) | |||
Timestamping a cell with a Link already attached to it. | Excel Worksheet Functions |