DateTime & Username stamp

[Spraggs]

Hello All,
I'm working in a spreadsheet Sheet1.
When any data is entered in F2:F2000 and saved, I would like to enter date/time & username stamp in the corresponding cells in column X Y & Z.
i.e. data entered in F27
X27 = 19/05/2010
Y27 = 15:21
Z27 = Joe Bloggs

Is this possible using Vba ?
If it is what is the code and how do I use it.

Any help is much appreciated.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F2:F2000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
    .Offset(, 18).Value = Date
    .Offset(, 19).Value = Time
    .Offset(, 20).Value = Environ("username")
End With
Application.EnableEvents = True
End Sub

 

[Spraggs]

Thanks very much.
Will this re-date etc if the cell in column F is changed.
i.e. If the data in cell F27 is changed at a later date will the columns in X Y & Z change to suit?
Apologies I can't try it myself because the sheet in question is at work.
Just one more question if I can..
I'm working on 2007 at home (2003 at work), is everything the same with regards this answer?

Much appreciated Jase

 

[Spraggs]

Brilliant VoG, works a treat!

 

[VoG]

Yes, any change in F will update X,Y and Z. Is that what you want - if not we can tweak the code.

It will work in any version of Excel from 97 to 2007.

 

[lenze]

I like to use a Comment for this!!
Tracking Changes with Comments

lenze

 

[Spraggs]

Thanks guys, brilliant.
I'll give the comment one a go, looks a great idea.

Thank You

 

[Spraggs]

Lenze,
This really useful, I've tried it on a new worksheet and it works perfect.
The problem I have is that I've already got some Vb....
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("P2:P2000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
.Offset(, 30).Value = Date
.Offset(, 31).Value = Time
.Offset(, 32).Value = Environ("username")
End With
Application.EnableEvents = True
End Sub

And a macro......
Sub Macro2()
ActiveSheet.Unprotect Password:="HALIFAX"
Range("A2:AV1000").Sort Key1:=Range("AP3"), Order1:=xlAscending, Key2:=Range("Q3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal
Range("A1").Select
ActiveSheet.Protect Password:="HALIFAX"
End Sub

When I entered the code .....
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 16 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 16 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub

This gives me a Microsoft Visual Basic _ Compile error.
Ambiguous name detected; Worksheet_change

(highlighted) Private Sub Worksheet_Change(By Val Target As Range)

Code written as below..
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("P2:P2000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
.Offset(, 30).Value = Date
.Offset(, 31).Value = Time
.Offset(, 32).Value = Environ("username")
End With
Application.EnableEvents = True
End Sub

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 16 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 16 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub

Could you please advise.
Regards Jase

 

[lenze]

You can only have One change Event in a Sheet module, so you will have to combine the 2 Events. Not that hard to do!! Something like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 16 Then
'Code for Checkmarks
ElseIf Not Intersect(Target, Range("P2:P200")) Is Nothing Then
'your other code
Else: Exit Sub
End If
End Sub

The other macros(Selection_Change and Macro2) will not be affected!
HTH
lenze

 

[Spraggs]

Lenze,
I'd love to get this code working, what is the full list of code required to get this working. I've tried for hours but had no joy.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F2:F2000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
.Offset(, 18).Value = Date
.Offset(, 19).Value = Time
.Offset(, 20).Value = Environ("username")
End With
Application.EnableEvents = True
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 16 Then
'Code for Checkmarks
ElseIf Not Intersect(Target, Range("P2:P200")) Is Nothing Then
'your other code
Else: Exit Sub
End If
End Sub



Regards Jase