Username (multiple usage)

[Spraggs]

Good evening,
I've got a table C2:N7 where employess enter data. I want to record the last person to fill in each cell by entering their username on another grid Q2:AB7. (i.e. C2 data, username entered in cell Q2 etc.)
Is this feasable? If so, how. e.g. what would the code be and where and how do I enter it. I'm using vista & excel 2007.

Any help is greatly appreciated.
Thanks Jase

 

[brettdj]

- right click your sheet tab
- View Code
- copy and paste in the code below

hth

Dave

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range
    Dim rng2 As Range
    Set rng1 = Intersect(Target, Range([c2], [n7]))
    If rng1 Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each rng2 In rng1.Cells
        rng1.Offset(0, 14) = Environ("username")
    Next
    Application.EnableEvents = True
End Sub

 

[Spraggs]

Brett, that is brilliant.
Thank You very much.
Jase

 

[brettdj]

No problem Jase, glad to have helped

Dave

 

[Spraggs]

Could I have help to advance on this....?

I've set up another two tables one to show date with an offset of 28. And another showing entry time, with an offset of 42. Do I need two more code entries or can it all be done in one?
Regards Jase

 

[wigi]

I would reduce your code lines and the number of write operations in the sheet, and go for:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range
    Set rng1 = Intersect(Target, [c2:n7])
    If rng1 Is Nothing Then Exit Sub
    For i = 1 To 3
        rng1.Offset(, 14 * i) = Environ("username")
    Next
End Sub

Wigi

 

[Spraggs]

Thanks Wigi, but how would this work with the date and time stamps with the offsets as quoted?

Regards Jase

 

[wigi]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range
    Set rng1 = Intersect(Target, [c2:n7])
    If rng1 Is Nothing Then Exit Sub
    For i = 1 To 3
        rng1.Offset(, 14 * i) = Choose(i, Environ("username"), date, time)
    Next
End Sub

 

[Spraggs]

Superb.
Thank You

 

[brettdj]

You should always disable events before writing new entries with Worksheet_Change to stop the code refiring ....

Cheers

Dave