User Record
 

Hey,

We have a shared workbook in which we want to record the user's name when
they enter an "X" in column A.

Is there a way to do this?

Thank you

User Record
 

Let's assume that the user's name will go in column B across from the "X"

Enter the following worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A")
Set t = Target
If Intersect(r, t) Is Nothing Then Exit Sub
If t.Value = "X" Then
Application.EnableEvents = False
t.Offset(0, 1).Value = Environ("username")
Application.EnableEvents = True
End If
End Sub

REMEMBER this is worksheet code, it does not go in a standard module
--
Gary''s Student - gsnu200747


"nabanco" wrote:

Hey,

We have a shared workbook in which we want to record the user's name when
they enter an "X" in column A.

Is there a way to do this?

Thank you

User Record
 

Enter this code in a normal module:

'http://www.j-walk.com/ss/excel/tips/tip94.htm

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

Function UserName() As String
' Returns the name of the logged-in user
Dim Buffer As String * 100
Dim BuffLen As Long
BuffLen = 100
GetUserName Buffer, BuffLen
UserName = Left(Buffer, BuffLen - 1)
End Function

NB: The PRIVATE DECLARE FUNCTION must be at the top of the module, before
any functions or procedures.

Then use either of these functions to return the logged on username:
=IF($A2="x",username(),"")
or
=IF(INDIRECT(ADDRESS(0,1,3,FALSE),FALSE)="x",usern ame(),"")

The second function will check column A of whichever row you enter the
formula on.

User Record
 

Actually, scratch that as the formula will update when a different user opens
the workbook.

"Darren Bartrup" wrote:

Enter this code in a normal module:

'http://www.j-walk.com/ss/excel/tips/tip94.htm

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

Function UserName() As String
' Returns the name of the logged-in user
Dim Buffer As String * 100
Dim BuffLen As Long
BuffLen = 100
GetUserName Buffer, BuffLen
UserName = Left(Buffer, BuffLen - 1)
End Function

NB: The PRIVATE DECLARE FUNCTION must be at the top of the module, before
any functions or procedures.

Then use either of these functions to return the logged on username:
=IF($A2="x",username(),"")
or
=IF(INDIRECT(ADDRESS(0,1,3,FALSE),FALSE)="x",usern ame(),"")

The second function will check column A of whichever row you enter the
formula on.

User Record
 

Hey Gary, for some reason I am having trouble with this one.

When you say this is a worksheet code, where do I go to place that in? Do I
just F11 and insert module? Or some other way?


"Gary''s Student" wrote:

Let's assume that the user's name will go in column B across from the "X"

Enter the following worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A")
Set t = Target
If Intersect(r, t) Is Nothing Then Exit Sub
If t.Value = "X" Then
Application.EnableEvents = False
t.Offset(0, 1).Value = Environ("username")
Application.EnableEvents = True
End If
End Sub

REMEMBER this is worksheet code, it does not go in a standard module
--
Gary''s Student - gsnu200747


"nabanco" wrote:

Hey,

We have a shared workbook in which we want to record the user's name when
they enter an "X" in column A.

Is there a way to do this?

Thank you

User Record
 

Right-click the worksheet tab and choose View Code.


In article ,
nabanco wrote:

Hey Gary, for some reason I am having trouble with this one.

When you say this is a worksheet code, where do I go to place that in? Do I
just F11 and insert module? Or some other way?