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

--
Message posted from http://www.ExcelForum.com

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

This would be the name when installed, and you can change in
ToolsOptionsGeneral User Name.

--

HTH

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

"Serum " wrote in message
...
Hey everyone. I was wondering how I could throw in some VB for a cell
that is like the last date modified codes, except that I want Excel to
input the user name of the person who made the last change into a
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 get
this username information from? Does it grab the name that was entered
when Excel was first installed? Is there a way to modify these things?
Without uninstalling/reinstalling?

Any help would be appreciated. Thanks.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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 " wrote in message
...
I'm actually having trouble now.

I'm supposed to throw this into a module right? Then I'm supposed to
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 As
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 format
everytime someone makes a change to the file. I would like to
basically achieve the same thing, but with the username changing when
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'd
still like the last time it was modified, and by whom, visible on the
worksheet itself. I tried modifying the above code to possibly insert
=lastauthor() into a specific cell, but to no avail.

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


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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 " wrote in message
...
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 the
spreadsheet. ie: I open it up, make a change, the UDF changes L2 to
display my name. Then say jack opens it, jack makes a change, I want
L2 to display his name.

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

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

I appreciate your help Bob.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

As I mentioned in the post, it doesn't get the Excel name from
ToolsOptions, 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 " wrote in message
...
Bob, or anyone, can you tell me where this code is looking for the
username? I've tried it on a few machines, and it doesn't seem to look
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 I
want. I changed a cell again, old username pops up.

I proceeded to search throughout the registry and change all the names
to the name I wanted. Reboot. Still, this other name pops up, and I
have no clue where it's coming from, especially since I completely went
through the registry. I changed the registeredowner key, multiple other
keys, changed the user account name, etc...still this old name persists
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 can
change it.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com