Macro to Add Windows Username in Cell

[Kickingkangaroos]

Hi All,

I have done a search on here to try to find what I'm looking for, but have been unable to get exactly what I need.

I "manage" a spreadsheet which multiple users access and update. It has columns B - I which the users can update; it's not shared, so users must enter it one at a time and save each time.

What I would like to add is a formula/function/macro (whatever it needs to be), to add the Windows Username of whoever has just updated the spreadsheet - specifically column B.

I.e. - User 1 opens the spreadsheet, enters new information for Row 121 (just as an example). I want there to be a function in column J, which automatically adds the Windows Username for User 1 into J121.

Then when User 2 opens up the spreadsheet, and enters new data in Row 122; the function runs again to enter the Windows Username of User 2 in J122.

Etc etc...

So there is an historic record of who input each Row into the spreadsheet.

Can this be done at all?

Any help would be much appreciated, and please let me know if you need me to provide a clear explanation of what I am trying to achieve.

Many thanks,

Andy

 

[VoG]

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

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
    Application.EnableEvents = False
    Target.Offset(, 8).Value = Environ("username")
    Application.EnableEvents = True
End If
End Sub

 

[Kickingkangaroos]

That's brilliant - thank you.

Just a couple of things to confirm:

1 - if data gets deleted from column B (and not replaced), is there a way for the username to be cleared from column J?

2 - will the username remain in column J, provided no-one changes the data on the same row, despite multiple users accessing the spreadsheet?

E.g. User 1 opens the s/sheet and changes row 10, so J10 is Username1 - User 1 then saves and closes the s/sheet; then User 5 opens the s/sheet and changes row 11, so J11 would be Username5 (and J10 would still be Username1) - the macro wouldn't alter existing data?

Thanks again for such a swift response - that's fantastic.

Andy

 

[VoG]

For 1. try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
    Application.EnableEvents = False
    If Target.Value <> "" Then
        Target.Offset(, 8).Value = Environ("username")
    Else
        Target.Offset(, 8).ClearContents
    End If
    Application.EnableEvents = True
End If
End Sub

For 2, the user name will remain until a change is made to column B.

 

[Kickingkangaroos]

That's absolutely great - thank you very much indeed for your help - fantastic!

 

[stephenscott]

I am looking to run something similar

A macro to populate the username into a cell when I open a excel template.

I have very basic knowledge and would appreciate assistance

 

[VoG]

I am looking to run something similar

A macro to populate the username into a cell when I open a excel template.

I have very basic knowledge and would appreciate assistance

Try this. Open your template, press ALT + F11 to open the Visual Basic Editor. In the project window double click ThisWorkbook and paste intothe white space on the right

Private Sub Workbook_Open()
Worksheets(1).Range("A1").Value = Environ("username")
End Sub

Press ALT + Q to close the code window then save, close and re-open the workbook to see the effect.

 

[stephenscott]

Thank you very much !

This provided my Login Username exactly as decsribed. Do you know if there is a Enviroment Variable which has the users email so I can insert? or should I build a email table which does a lookup for the username and populates the full email address? and shows it in a email/hyperlink format.ie stephen@hotmail.com

Unfortunately we have 2 different email clients Lotus Notes and Outlook so if there is a macro I will need both options.

 

[VoG]

As far as I know there isn't an environment variable for that so you'd need to use a lookup table.

 

[stephenscott]

Thanks Cheers !