Excel excel function 'username'

B

bloglog

Joined
Feb 7, 2006
Messages
1
Reaction score
0
i'm tracking projects and any updates that are made in excel. currently using =NOW() which updates the date and time each time a user
makes ANY changes on the page. works great.

i would also like to track the user since this will be on a shared directory. i am aware of Track Changes under Tools but did not want to use.

so far i've googled excel functions, getusername, username, UserName = Environ("UserName")
and do find what i believe are solutions, but continue to get.... #NAME? error.

here are the steps i take below to add the function:

go to tools - macro - vb editor
add:
Function username()
username = Application.username
End Function
save as name.xla in addins

alt Q out of vb editor

on spreadsheet enter Shift F3...make sure the add in is checked.

in the specific cell i would like the username to show, i use =applicaton.username() or =username() or ="username"()

continue to get #NAME

any suggestions? am i totally off? :)
 
i may be able to help

i dont play with excel, bt VB is my specialty, and i understand that acess and excel work very similarly with it.

i found that access's default "username" intrinsic returned "admininstrator" or Null, regardless of NT user

so....
I wrote a module, and a function.
the function collects workstation info, and stores them to variables.

i later bring these up with the following code


With Text0
.SetFocus
.Text = Module1.UserName
End With

the username is a variable, and so the function must be called first to save variable

Module1.GetWorkstationInfo

i did this in the open action of the form.
You may need to do this by macro.


your cell could possibly reference the module too.

or edit the macro that calls the function, to then save "module1.username" to a cell.

several other details can be pulled to, those variables can be called the same as user and are:

computername
logondomain
langroup
(username)



BELOW IS CODE FOR INFO GATHERER...(CREATE(PASTE) IN A MODULE)

FIRST DECLARE ALL VARIAVBLES AND TYPES(JUST COPY AND PASTE AT TOP of new module OVERWRITING ANy OPTION STATEMENTS THEAT MAY EXIST.)

Option Explicit

Type WKSTA_INFO_101
wki101_platform_id As Long
wki101_computername As Long
wki101_langroup As Long
wki101_ver_major As Long
wki101_ver_minor As Long
wki101_lanroot As Long
End Type
Type WKSTA_USER_INFO_1
wkui1_username As Long
wkui1_logon_domain As Long
wkui1_logon_server As Long
wkui1_oth_domains As Long
End Type
Declare Function WNetGetUser& Lib "Mpr" Alias "WNetGetUserA" _
(lpName As Any, ByVal lpUserName$, lpnLength&)
Declare Function NetWkstaGetInfo& Lib "Netapi32" _
(strServer As Any, ByVal lLevel&, pbBuffer As Any)
Declare Function NetWkstaUserGetInfo& Lib "Netapi32" _
(reserved As Any, ByVal lLevel&, pbBuffer As Any)
Declare Sub lstrcpyW Lib "Kernel32" (dest As Any, ByVal src As Any)
Declare Sub lstrcpy Lib "Kernel32" (dest As Any, ByVal src As Any)
Declare Sub RtlMoveMemory Lib "Kernel32" _
(dest As Any, src As Any, ByVal size&)
Declare Function NetApiBufferFree& Lib "Netapi32" (ByVal buffer&)
Public UserName As String
Public computername As String, langroup As String, logondomain As _
String



THEN PASTE IN FUNCTION TO BE CALLED(just below)...

Function GetWorkstationInfo()
Dim ret As Long, buffer(512) As Byte, i As Integer
Dim wk101 As WKSTA_INFO_101, pwk101 As Long
Dim wk1 As WKSTA_USER_INFO_1, pwk1 As Long
Dim cbusername As Long


' Clear all of the display values.
computername = "": langroup = "": UserName = "": logondomain = ""
' Windows 95 or NT - call WNetGetUser to get the name of the user.
UserName = Space(256)
cbusername = Len(UserName)
ret = WNetGetUser(ByVal 0&, UserName, cbusername)
If ret = 0 Then
' Success - strip off the null.
UserName = Left(UserName, InStr(UserName, Chr(0)) - 1)
Else
UserName = ""
End If
'NT only - call NetWkstaGetInfo to get computer name and lan group
ret = NetWkstaGetInfo(ByVal 0&, 101, pwk101)
RtlMoveMemory wk101, ByVal pwk101, Len(wk101)
lstrcpyW buffer(0), wk101.wki101_computername
' Get every other byte from Unicode string.
i = 0
Do While buffer(i) <> 0
computername = computername & Chr(buffer(i))
i = i + 2
Loop
lstrcpyW buffer(0), wk101.wki101_langroup
i = 0
Do While buffer(i) <> 0
langroup = langroup & Chr(buffer(i))
i = i + 2
Loop
ret = NetApiBufferFree(pwk101)
' NT only - call NetWkstaUserGetInfo.
ret = NetWkstaUserGetInfo(ByVal 0&, 1, pwk1)
RtlMoveMemory wk1, ByVal pwk1, Len(wk1)
lstrcpyW buffer(0), wk1.wkui1_logon_domain
i = 0
Do While buffer(i) <> 0
logondomain = logondomain & Chr(buffer(i))
i = i + 2
Loop
ret = NetApiBufferFree(pwk1)
End Function

I hope that helps, ,let me know how it works.
 
Back
Top