User ID'S

  • Thread starter Thread starter james
  • Start date Start date
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.
 
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]
 
Back
Top