CODEWITHSUNDEEP
vbaexcel
ex-patriot
ex-patriot

Reputation: 97

Excel - Grab Username for who ever last modified a cell

is it possible to have some VBA to record the user for who ever last modified a specific cell? i.e., if someone opens the workbook and enters a value into A1, I'd like B1 to show the username of the person who did that, and then, if someone else opens the workbook and enters a value into A2, i'd like their user name in B2 and so on and so forth... I've playing around with examples like the below, but I'm not sure if I'm getting any closer, seems i can only grab the username for whoever last modified the workbook.

Function LastAuthor()
    LastAuthor = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
End Function

Upvotes: 2

Views: 10586

Answers (1)

user4039065
user4039065

Reputation:

This inserts the windows user/network user in column B whenever one or more values are added/modified/deleted in column A.

Add this to the worksheet's private code sheet; not a public module code sheet.

private sub worksheet_change(byval target as range)
    if not intersect(range("A:A"), target) is nothing then
        on error goto meh
        application.enableevents = false
        dim t as range
        for each t in intersect(range("A:A"), target)
            t.offset(0, 1) = environ("user")
            't.offset(0, 2) = now
        next t
    end if
meh:
    application.enableevents = true
end sub

Upvotes: 3

Related Questions