Save username if cell has been changed by user

[krlouvae]

Hey guys,

Is it possible to save or write an user name to a cell, if that user changes a value?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]value w[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]value x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]value y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]value z[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If user changes 'value w' (change: to another text, string, or even delete the value), B1 should show user name.
If user changes 'value x' (change: to another text, string, or even delete the value), B2 should show user name.
If user changes 'value y' (change: to another text, string, or even delete the value), B3 should show user name.
If user changes 'value z' (change: to another text, string, or even delete the value), B4 should show user name.

Thx in advance !
Kristof

 

[steve the fish]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A4")) Is Nothing Then
    Range("B" & Target.Row).Value = Environ("Username")
End If
End Sub

 

[Peter_SSs]

I would expand on Steve's code a little as it is possible the user could change multiple cells at once (eg by pasting or deleting) and I would disable events to the code isn't re-called when the user name is entered in column B

I also add the note that both our codes will still enter the user name in column B if the user edits a cell value but doesn't actually change the value (eg typing "value x" in a cell that already contains "value x").

And in case you are not familiar with this type of code, to implement it ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

My alternative code (which also looks for a change anywhere in column A - wasn't sure if you meant to check only 4 cells or not) is:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, Cell As Range
  
  Set Changed = Intersect(Target, Columns("A"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each Cell In Changed
      Cell.Offset(, 1).Value = Environ("Username")
    Next Cell
    Application.EnableEvents = True
  End If
End Sub

 

[krlouvae]

Great and extremly good fast help guys!!! Thx !!!!

I would go a bit further now:
COL A = values
COL B = user name of the one who makes changes in COL A
COL C = values
COL D = user name of the one who makes changes in COL C
COL E = values
COL F = user name of the one who makes changes in COL E

 

[krlouvae]

And is the user name taken from Windows or from Excel?
If from Excel, where do I change that?

 

[Peter_SSs]

Great and extremly good fast help guys!!! Thx !!!!

I would go a bit further now:
COL A = values
COL B = user name of the one who makes changes in COL A
COL C = values
COL D = user name of the one who makes changes in COL C
COL E = values
COL F = user name of the one who makes changes in COL E

  Set Changed = Intersect(Target, Range("A:A,C:C,E:E"))

And is the user name taken from Windows or from Excel?
If from Excel, where do I change that?

Windows.

 

[krlouvae]

Great man, many thanks!!!!

 

[Peter_SSs]

No problem, glad to help.