"Last modified by" in a cell.

[Serum]

Hey everyone. I was wondering how I could throw in some VB for a cel
that is like the last date modified codes, except that I want Excel t
input the user name of the person who made the last change into
specific cell. If it's possible, how would I go about doing it?

Also, if this is possible and I throw the code in, where does Excel ge
this username information from? Does it grab the name that was entere
when Excel was first installed? Is there a way to modify these things
Without uninstalling/reinstalling?

Any help would be appreciated. Thanks

 

[Bob Phillips]

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

This would be the name when installed, and you can change in
Tools>Options>General User Name.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Serum]

Bob, thanks for your help, I really appreciate it. Works like a charm

 

[Serum]

I'm actually having trouble now.

I'm supposed to throw this into a module right? Then I'm supposed t
input =lastauthor() into whatever cell I want this to show up right?
It's not working correctly.

I need something like the following:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)
Worksheets("PSR").Range("L1:Q1").Value = Format(Now, "mm/dd/yy
hh:mm:ss")
End Sub
_______________________________________________

This changes a range of cells into the specific date/time forma
everytime someone makes a change to the file. I would like t
basically achieve the same thing, but with the username changing whe
changes are made as well. I hope I'm being clear.

The file will be shared on the network, with multiple people using it.
While I'm aware I can share the document and track changes that way, I'
still like the last time it was modified, and by whom, visible on th
worksheet itself. I tried modifying the above code to possibly inser
=lastauthor() into a specific cell, but to no avail.

I should say I don't have much VB coding experience, well, codin
experience in general. Once again, I'd appreciate any help in settin
this up

 

[Serum]

Well, seems I'm getting closer to figuring this out. Found this in th
forums...

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


It seems to work for a second, then the cell is replaced with VALUE!
The thread said to format the cell to Date, but I've done that and
keep gettin VALUE! Everytime I double click on the cell however, I se
my username, but then it reverts right back. Anyone have any ideas?
can't believe I've been working on this most of the day hahaha

 

[Bob Phillips]

That is not what you asked for, you asked to get the name of the user who
made the last change in a cell. Now you are asking to update the worksheet
when a change is made. What exactly do you want?

Regards the other response from you, you are also looking at one of my
earlier posts. The guy wanted a date, that was why I suggested formatting
the cell as date (obvious eh?). It also needs a parameter saying what
property, I bet my post said =DocProp("Last Save Time")

Tell me exactly what you want, then I may be able to help. By this, I mean
what information you want, what will trigger it, will it be changes, if so
to what sheet, what cells, etc. Do you want the logon user rather than
volatile Excel name?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Serum]

Bob,

Ok, sorry for not being so clear in my posts. Here's what I need.

I need a cell (L2) to display the last author who made a change to th
spreadsheet. ie: I open it up, make a change, the UDF changes L2 t
display my name. Then say jack opens it, jack makes a change, I wan
L2 to display his name.

So, basically I want whoever the last person was to make any change
whatsoever, to be displayed in L2.

I hope I'm wording what I want better. If it's still not clear, let m
know and I'll try try again.

I appreciate your help Bob

 

[Bob Phillips]

Okay, here we go.

Add this code to a standard code module

Option Explicit

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" _
(ByVal lpBuffer As String, _
nSize As Long) As Long

Public Function UserName() As String
Dim sName As String * 256
Dim cChars As Long
cChars = 256
If GetUserName(sName, cChars) Then
UserName = Left(sName, cChars - 1)
End If
End Function

Then add this code to the worksheet


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
Range("A1").Value = UserName

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


Wht this will do is any time theworkbook is change, cell A1 will get updated
with the login name. Change the A1 to suit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Serum]

Bob,

Works great. I wish I could return the helping hand. While I'm at it
are there any books you would recommend that would help me tak
advantage of Excel, or VB for that matter? Also, if it's not too muc
of a hassle and you have time, do you think you could write a littl
explaining what parts of the code does what? I want to be able t
understand it a little more.

Once again, thanks for your help

 

[Serum]

Bob, or anyone, can you tell me where this code is looking for th
username? I've tried it on a few machines, and it doesn't seem to loo
at the author field in the tools/options of excel.

What's odd is, on one machine, I threw the code into the worksheet
changed a cell, a username pops up.

I then proceed to change the name under tools/options to the name
want. I changed a cell again, old username pops up.

I proceeded to search throughout the registry and change all the name
to the name I wanted. Reboot. Still, this other name pops up, and
have no clue where it's coming from, especially since I completely wen
through the registry. I changed the registeredowner key, multiple othe
keys, changed the user account name, etc...still this old name persist
on coming up when I modify a cell. This is on a machine w/XP Pro.

I just need to know where it's getting the username from so I ca
change it

 

[Bob Phillips]

As I mentioned in the post, it doesn't get the Excel name from
Tools>Options, as this is not robust enough. What it does its get your
network login id, the one you login to Windows with, as this is more robust.

To prove it works, get another user to open the workbook and change it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Serum]

It definitely works, that's for sure. Now I'm just having a hard tim
w/getting correct names to show up. I've been all through th
registry, user accounts, etc...but that's not an Excel problem so
won't ask again.

Thanks again for helping me get this working Bob

 

[cconley23]

Hi there for anyone:
I am posting on this old thread because this talks about something similar to what I am needing. If no response, I will try a new thread.

I would like to grab the user name of whoever modified a certain cell. The workbook spreadsheet would be shareable via SharePoint. Would really rather not use Track Changes.

Example:

In cell A5:
User "John S." types a string of text/ numbers.

In cell C5:
After John presses enter, C5 auto populates with his user name.

Then would then repeat on down column C.

User Susan B. then goes and grabs the workbook and types in some text/ numbers in A6 with C6 auto populating with her username. Cell C5 still shows John S modified A5.

And so on, and so on. Below is an example of what I am talking about. Right now, column B would need to be manually typed in.

There was a VB code I tried that provided a "=getLastModifiedUser()", however that was not working as it would keep returning the user actively typing in on all the cells within column B that has the formula.

Thanks!