Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
45 RPM Record Catalog | Excel Discussion (Misc queries) | |||
How do I format a cell, so the user must use a user calendar? | Excel Discussion (Misc queries) | |||
How do I format a cell, so the user must use a user calendar? | Excel Discussion (Misc queries) | |||
Macro to record user name and date/time | Excel Discussion (Misc queries) | |||
Finding a record based on user input | Excel Discussion (Misc queries) |