User ID'S

J

james

i have a spreadsheet in Excel 2000. in a shared notebook, the user's ID is
captured in the comments. is there a way to autofill a cell to capture a
user id upon open? in other words, I'd like for a form to capture the users
id in cell A1 when they open the form. I'd also like this not to change if
someone else opens the same form.
 
J

Jake Marx

Hi James,

You can get the username from one of two places:

1) an environment variable:

MsgBox Environ$("Username")

2) the Windows API:

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

Public Function gsGetUsername() As String
Dim sName As String * 255
Dim nPos As Integer

GetUserName sName, 255
nPos = InStr(1, sName, vbNullChar)
If nPos Then gsGetUsername = Left$(sName, nPos - 1)
End Function

MsgBox gsGetUsername

I typically use #2, as I feel it is more reliable than #1. In your
Workbook_Open routine (double-click ThisWorkbook in the VBE to get to the
applicable codepane), you could check to see if a cell has a value - if not,
put the username there:

Private Sub Workbook_Open()
If Len(Sheets("Sheet1").Range("A1").Value) = 0 Then
Sheets("Sheet1").Range("A1").Value = gsGetUsername
End If
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Top