Excel VBA Macro to Get Username
Using Excel VBA, get username who is currently logged into Windows PC or get author name who edited the Excel workbook, Ms Office Username, Network userid etc.,
Refer the below table to understand what kind of username you can fetch and its corresponding command.
Before using them, read further in detail in below sections, so that you use the correct variant.
|1. Get MsOffice/Excel username||Application.username|
|2. Windows Active Logged in Username||Environ(“Username”)|
|3. Windows API||apiGetUserName|
|4. Network Username||WScript.Network|
|5. VBA Username from Windows Registry||CreateObject(“wscript.shell”).
|6. Get the Author name or
Username who edited an Excel workbook
Well that’s piece of cake if you know bit of VBA already. But, What if I say there are more ways!!!
Would You be interested in knowing them as well?
Lets find out.
Different Method to Find Windows UserName
Let’s see them one by one in the order of how frequent they are used.
- System Environment Variables – Windows login user
- Application.username – MS Office user name
- Windows API ‘apiGetUserName’ Function
- Network UserName
- Username from Windows Registry key
In some cases, if the easiest one did not work out, follow the next possible method.
1. Insert Windows Username in Excel Cell
If you would like to get username of Windows login to a worksheet cell, remember there is no Excel formula or built-in function to do this.
Good news is that you can use the below code. this can act as a user defined function or custom Excel formula.
To do this, press Alt+ F11 from your Excel file,then go to VB editor, insert a new module & paste this code.
Function GetUserName() As String GetUserName = Environ$("username") 'or 'GetUserName = Application.UserName End Function
Then open the worksheet, insert this formula “=GetUserName()” and press “Enter” key.
Now, the cell will execute the function and display the Windows logged in username in the cell.
2. VBA Application.Username – Excel User
Well this is the easiest of all the method and does not require any references to be added to any library or DLL.
You can manually change this user name from this menu option: File – Options – General as in this image.
To get this Username in a VBA code, we have built-in function. Use the below code in your Excel or Word VB Editor and run it.
Sub GetUserName_AppUser() Dim strUserName As String 'Use the Application Object to get the Username strUserName = Application.UserName MsgBox "Current Logged In UserName is:" & strUserName End Sub
This Excel macro code will display the current Windows username, who is also executing this Excel or Word App.
The above two are the most used methods.
In case you are interested more in this area, then refer the other methods.
3. Excel VBA Get Username from System Environment Variables
Open Command prompt and type ‘Set’ and press Enter. This command will display list of all the Environment variables and values stored in it. In this list, we have an Environment variable “USERNAME” and it will have the active logged in username for that session.
We can write macro to read this Environment Variable and get the user id.
Sub GetUserName_Environ() Dim idx As Integer 'To Directly the value of a Environment Variable with its Name MsgBox VBA.Interaction.Environ$("UserName") 'To get all the List of Environment Variables For idx = 1 To 255 strEnvironVal = VBA.Interaction.Environ$(idx) ThisWorkbook.Sheets(1).Cells(idx, 1) = strEnvironVal Next idx End Sub
Note: This VBA macro can also be used to read all the Environment variables from system for that login session.
4. Using Windows API ‘apiGetUserName’ Function
Microsoft provides many other variants of this function that get username within Excel VBA.
APIs based on your need. To use this API, declare the function before calling the function.
'For 32-bit office installation 'Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _ ' "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long 'For 64-bit office installation Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" _ Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As LongPtr) As Long Sub Get_UserName_WINAPI() 'Declare Variables Dim Returned_Val_Length As Variant Dim API_Return_Status As Variant Dim Returned_UserName As String 'Define Space for Variables Returned_UserName = String$(254, 0) Returned_Val_Length = 255 'Call the API and get the User Details API_Return_Status = apiGetUserName(Returned_UserName, Returned_Val_Length) 'Remove unwanted details from Return value and get only Username Returned_UserName = Mid(Trim(Returned_UserName), 1, Returned_Val_Length - 1) End Sub
5. Get Network UserName in Excel VBA
The WScript Object is used here to get the Network Username for the current logged in user.
Create an instance for the object Wscript.Network and use it to fetch the User and Computer Details as explained below.
Sub GetUserName_Environ() Dim ObjWshNw As Object Set ObjWshNw = CreateObject("WScript.Network") MsgBox ObjWshNw.UserName MsgBox ObjWshNw.ComputerName MsgBox ObjWshNw.UserDomain End Sub
All these combination of codes, fetch current Windows user name details to Excel.
6. Username From Windows Registry
Windows system stores numerous detail in its registry. Details like Operating system features, parameters, installed software, users list & details, etc.,
With this Excel vba get username and user details from registry of a windows OS to the Excel worksheet.
Function getUsernameWindows() As String getUsernameWindows = CreateObject("wscript.shell").RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\Common\UserInfo\UserName") End Function
We will be in need of these code tricks when, We share any Office document with other users and we need to collect the user details whoever is accessing it or restrict users from using this document based on their login id.