B
billservit
I need to timestamp a cell when the value of another cell changes from blank
to non-blank.
to non-blank.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
You need VBA else the time will chnage frequently
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
If Target.Value = "" Then
Range("B1").Value <> Format(Time, "hh:mm:ss")
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
If Target.Value = "" Then
Range("B1").Value <> Format(Time, "hh:mm:ss")
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
R
RCW
If you wanted to time, date, and user stamp every change made in the first
300 rows you can do something like this. The stamps for A1:Z300 would
appear in AA1:AZ300 off creen to the right or you could hide that area so
only you would know it's there. Just change the offset if you want it
somewhere else. If the currently active Offset and Application.Username
lines don't produce the name you expect, comment them out and activate the
offset and Environ("username") lines by changing the apostrophes at the
beginning of those two lines. Paste this into the worksheet by right mouse
clicking the worksheet tab and selecting View Code.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:Z300")) Is Nothing Then
With Target
.Offset(0, 26).Value = Format(Now, "dd mmm yyyy hh:mm ") _
& Application.UserName
'.Offset(0, 26).Value = Format(Now, "dd mmm yyyy hh:mm ") _
'& Environ("username")
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
300 rows you can do something like this. The stamps for A1:Z300 would
appear in AA1:AZ300 off creen to the right or you could hide that area so
only you would know it's there. Just change the offset if you want it
somewhere else. If the currently active Offset and Application.Username
lines don't produce the name you expect, comment them out and activate the
offset and Environ("username") lines by changing the apostrophes at the
beginning of those two lines. Paste this into the worksheet by right mouse
clicking the worksheet tab and selecting View Code.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:Z300")) Is Nothing Then
With Target
.Offset(0, 26).Value = Format(Now, "dd mmm yyyy hh:mm ") _
& Application.UserName
'.Offset(0, 26).Value = Format(Now, "dd mmm yyyy hh:mm ") _
'& Environ("username")
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
J
Jim May
I think Bob meant line 5 and 6 to be:
If Target.Value <> "" Then
Range("B1").Value = Format(Time, "hh:mm:ss")
End If
HTH
If Target.Value <> "" Then
Range("B1").Value = Format(Time, "hh:mm:ss")
End If
HTH
B
Bob Phillips
and I thought I tested that :-(
Bob
Bob
Jim May said:I think Bob meant line 5 and 6 to be:
If Target.Value <> "" Then
Range("B1").Value = Format(Time, "hh:mm:ss")
End If
HTH