Determine Users Login ID & set cell value based on a changed cell

[DTM]

I would like to set the value of a cell to the users login id when
cell in the row is changed without causing all the other cells t
recalculate. The problem I am having is that I cannot determine th
users login id (I can use the Application.username but this value ca
be changed in properties) and if I use formulas, it recalculates al
the cell in the column I am setting the cells value.

So:

1. How do I determine the users login ID?

2. How can I determine if a specific cell changes and then set th
value of one single cell

 

[Bob Phillips]

Here is some code that does all that you want.

It checks any cell in column A, and if changed puts the login id in B

Put this code in the worksheet code module (rfight-click on the tab name,
select View Code, and paste the code in)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 1 Then
Target.Offset(0, 1).Value = UserName
End If

ws_exit:
Application.EnableEvents = True
End Sub


'----------------------------------------------------------------------

Put this code in a standard code module

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



--

HTH

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

 

[DTM]

Thank you. It works great.

Da

 

[DTM]

To see the solution, go to the link below.

http://www.excelforum.com/t198970-

 

[Bob Phillips]

or even here

http://www.google.com/[email protected]&rnum=2

--

HTH

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