Add the cell content editors name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Johnny7 - 07 Mar 2007 16:56 GMT

I'm creating a spreadsheet that different users will use on a network of
computers, with there own "sign ons" as such (its at my place of work).

Different users will be adding dates for when items have been received. for
example cell a1 would be the date they have entered, and cell a2 would have
the name of the person who added the date, once they have entered the date in
a1, id like a2 to automatically enter the authors name.

so...
a1(date manually entered) a2(authors name, added auto once date has been
added)

Is there anyway to do this? I'm using Excel 2000

Many thanks,

John

Reply to this Message

vezerid - 07 Mar 2007 17:52 GMT

> I'm creating a spreadsheet that different users will use on a network of
> computers, with there own "sign ons" as such (its at my place of work).
[quoted text clipped - 13 lines]
>
> John

What would be the criterion for determining who the user is? Some
login process? Guesswork? A table of expected dates and names?

HTH
Kostis Vezerides

Reply to this Message

Gord Dibben - 07 Mar 2007 19:51 GMT

Johnny

Which username would you like to have entered?

Login name or Application name.

Run this macro to see what you get in the message boxes then post back which one
you want to show. The we'll build you some event code.

Sub which_username()
MsgBox Environ("UserName") 'login name
MsgBox Application.UserName 'application name
End Sub

I'm betting on login name because you are on a network.

I'm also betting you will want to lock the cell after the name has been entered.

Also you will have more than A1 to fill in a name.

Try this event code in the sheet module.

Assumes column A and B are unlocked and sheet is protected with a password of
"justme"

Enters the login name into column B when column A has a date entered.

Then locks the column B cell.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Column = 2 Then
On Error GoTo enditall
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="justme"
n = Target.Row
If Target.Value <> "" _
And Target.Offset(0, 1).Value = "" Then
With Target.Offset(0, 1)
.Value = Environ("username")
.Locked = True
End With
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

Gord Dibben MS Excel MVP

>I'm creating a spreadsheet that different users will use on a network of
>computers, with there own "sign ons" as such (its at my place of work).
[quoted text clipped - 13 lines]
>
>John

Reply to this Message

Gord Dibben - 07 Mar 2007 23:37 GMT

Johnny

Error in last post..................

Should read Assumes column A is unlocked and sheet is protected with a password
of "justme"

Gord

>Johnny
>
[quoted text clipped - 64 lines]
>>
>>John

Reply to this Message