In Excel, you can get the Username by using Application.UserName
. The Username is what is entered on the General tab of Tools > Options. Inexplicably, you cannot get the user’s initials, but you can in some other Office programs. I read a suggestion to automate Publisher and get the user initials via that object model. That seems a little extreme to me.
This is what I’m using. It gets the first character of, up to, the first three words in Application.Username.
Public Function UserInitials() As String
Dim vaNames As Variant
Dim sInit As String
Dim lMax As Long
Dim i As Long
vaNames = Split(UCase(Application.UserName), ” “)
lMax = Application.WorksheetFunction.Min(2, UBound(vaNames))
For i = 0 To lMax
sInit = sInit & Left$(vaNames(i), 1)
Next i
UserInitials = sInit
End Function
Dim vaNames As Variant
Dim sInit As String
Dim lMax As Long
Dim i As Long
vaNames = Split(UCase(Application.UserName), ” “)
lMax = Application.WorksheetFunction.Min(2, UBound(vaNames))
For i = 0 To lMax
sInit = sInit & Left$(vaNames(i), 1)
Next i
UserInitials = sInit
End Function
Some testing:
And in the off chance that you need to do a whole bunch of names stored in a worksheet, here’s a formula solution:
This uses the Excel 2007 IFERROR function. The equivalent formula for earlier versions is a lot messier.
Dick, this doesn’t work in many corporate environments, where the Excel username is set to the company name. Even if it isn’t, PCs get swapped around, and users don’t bother resetting the name, so the username may not be the current user anyway.
The code below uses an API call to get the Windows login name,which should always give you the actual user name.
Declare Function WNetGetUser Lib “mpr.dll” Alias “WNetGetUserA” (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long
Function GetUser() As String
Dim S As String * 255
S = Space(255)
Call WNetGetUser(vbNullString, S, 255&)
GetUser = Left$(S, InStr(S, vbNullChar) – 1)
End Function
dermot: That’s just what I need. After I wrote that someone tried to “sign” a worksheet from a different computer and they were shocked when it didn’t use their initials. If I can get the Windows name, it will follow people around.
I use this (UserFullName) to get the users full name from Active Directory. Just remember to change the :
Alias “GetUserNameA” (ByVal lpBuffer As String, nsize As Long) As Long
Function fOSUserName() As String
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX 0 Then
fOSUserName = Left$(strUserName, lngLen – 1)
Else
fOSUserName = “”
End If
End Function
Function UserFullName()
Dim MyUser As IADsUser
On Error Resume Next
Set MyUser = GetObject(“WinNT:///” & fOSUserName & “,user”)
UserFullName = MyUser.FullName
End Function
– Asser
Up to 3? What about people with 4 names? What about hyphenated names? What about two co-workers who use their common names Beth Sawyer and Bob Smith – not ambiguous – but distinguish their initials by using their actual first names and/or including their middle initials, so maybe ES and RAS? Not to mention that both might be touchy about their documents being littered with BS.
All shortcuts have pitfalls. This one is likely to have larger pitfalls than most.
Dermot’s udf returns the system user ID, which does identify the user, but usually not in a way that mere humans can use. E.g., my own user ID at work includes decimal numbers and none of the letters from my actual name. IOW, it’s closer to my car’s license plate number than my name.
But if that’s all you want, and you’re in a corporate environment, you’d usually be safe using either of the following and avoiding API calls.
Dim s As String
s = Environ(“USERPROFILE”)
userid1 = Mid$(s, InStrRev(s, “”) + 1)
End Function
or
bar = CreateObject(“WScript.Network”).UserName
End Function
1) For Jazzer’s code to work you need to set a reference to
– Active DS Type Library (in activeds.tlb)
But, on my system (on corporate network), both functions return empty strings.
2) fzz’s function returns
C:Documents and Settingsjohn_dowe.ACMEPLC
3) In view of (2) Dick’s code needs to split on “_” for me.
Interesting discussion …
Dermot, Dick, you don’t need an API call.
Just use:
Environ(“USERNAME”)
Dermot:
“The code below uses an API call to get the Windows login name,which should always give you the actual user name.”
Well, at one company I had a windows login name like “e013408? and at another I was Peter somebody, because the IT genius didn’t know how to change the ID or create a new one for me.
Jon Peltier said “…at one company I had a windows login name like “e013408? and at another I was Peter somebody, because the IT genius didn’t know how to change the ID or create a new one for me.”
Well, that would still be your actual [b]user[/b] name wouldn’t it?
As long as the name is assigned to one user and not being shared the name itself doesn’t have to be the same as the users [i]real[/i] name…
Next, you just need to know who belongs to each username!
Dick said:
“If I can get the Windows name, it will follow people around.”
I already have enough people following me, and not because I’m a leader.
Also, in an office using distributed files, the version you query might not be from the user who originated the file, or last actually updated it, only the person whose document store it was found in.
Or am I missing the point?
@Mpemba – I’d guess you mean the userid1 function. Unless you’re using Excel 97 or prior and use your own InStrRev, I can’t see how Excel wouldn’t return 27, and therefore how Excel wouldn’t have returned john_dowe.ACMEPLC. If thisn’t your actual user ID, then fine, this wouldn’t work.
My second function, which I had meant to rename as userid2 but left as bar, DOES return the user ID of the current user as long as Windows Script Host is installed on the machine. Since that’s been the default since IE5, it’s a reasonable assumption anyone in a corporate, governmental or academic environment where they replace PCs every 3 or 4 years has WSH on their work machine.
@Al – the USERNAME environment variable could be modified by the user, so it’s not absolutely guaranteed to be correct. But it’s simpler than parsing it from USERPROFILE, which may have other problems.
There’s always querying the regisrty. The current user’s ID would always be in the VALUE
HKCUSoftwareMicrosoftWindowsCurrentVersionExplorerLogon User Name
If you go to the extent of read the registry you can get at the Initials that the OM does not expose.
HKCUSoftwareMicrosoftOfficeCommonUserInfoUserInitials
Thanks for the registry tip Fzz:
This is much better easier that the Windows API route.
Dim myWS As Object
Dim strUser As String
Set myWS = CreateObject(“WScript.Shell”)
strUser = myWS.RegRead _
(“HKCUSoftwareMicrosoftWindows” & _
“CurrentVersionExplorerLogon User Name”)
End Function
Not too happy about the way the code shows here. Hopefully this is cleaner>
Dim myWS As Object
Dim strUser As String
Set myWS = CreateObject(“WScript.Shell”)
strUser = myWS.RegRead _
(“HKCUSoftwareMicrosoftWindowsCurrentVersionExplorerLogon User Name”)
MsgBox strUser
End Function
So many ways to get the logged user name. For years, I’ve used API calls to do this. Dermot and Fzz both demonstrate ways that are much simpler than my old way of doing this.
Any thoughts on which method may be the most efficient/effective?
I did not know about that method to read the registry. Much simpler than API calls.
Isn’t it true that Windows Scripting Host is often disabled for security reasons?
Security. It’d be nice if Windows could provide it.
Unless organizations’ IT departments are sophisticated enough to use alternative credentials for logon scripts when users log on, or just don’t use logon scripts, there’s some scripting engine somewhere on users’ systems which users can run. Maybe in a fully locked down environment users can only run .EXEs under Program Files and a few .EXEs under Windows or WinNT. In such circumstances, API calls may be all there is, but that begs the question whether greater security is achieved forcing users to make API calls rather than providing an easier to use scripting facility like WSH.
This also raises the question whether, from a security perspective, it’s a good thing VBA can make API calls. And if Microsoft adds a security feature to disable API calls in some future VBA version or successor scripting facility, then what?
Word can’t do without userinitials.
When my username in Word is Hans Schraven it automatically creates my initials: HS.
If I empty my username (options), Word automatically takes the computername as username and creates the initials accordingly.
To retrieve the userinitals in Excel:
With CreateObject(“Word.Application”)
c0 = UCase(.userinitials)
.Quit
End With
Hans, I posted codes similar to that last week but my comments are not shown (probably in the spam folder due to including a link for further details)…
Word and Visio seem to be the only 2003 apps that have complete access to (application) User Name and Initials.
@Simon, sorry to ‘overwrite’ you.
The nice thing about Word is that does your work: for instance
With CreateObject(“Word.Application”)
.username=“Simon Herbert”
c0 = UCase(.userinitials)
.Quit
End With
Hans – no problem…
Trying to post my code again:
Set objword = CreateObject(“Word.Application”)
MsgBox “Name: “ & objword.UserName
MsgBox “Initials: “ & objword.UserInitials
objword.Quit
(Dick, could you check if my previous posts are in your spam bin? Thanks!)
[…] could be to automatically load user’s name based on windows login ID. For more on this, see this article on […]