This function can be used to retrieve one of three types of usernames, and can be used in a worksheet cell or in VBA.
The usernames that it can return are:
- Microsoft Office username (default) as defined in Tools|Options|General
- Microsoft Windows logon name
- Computer name (as defined in your system properties)
Examples of where this function shines:
- When designing templates for use by multiple users --> Automate the task of entering the user's name(s) on the worksheet!
- When building macros --> Have your code execute a certain way depending upon the logged in user, and/or the machine that they are logged in on!
- The Microsoft Office username must be entered correctly (defined in Tools|Options|General) or it will return incorrect results
- Running in an environment where people log in sharing a Windows profile doesn't work well, as the computer can't identify who the user really is
- Must use the Application.Volatile keywords to force recalculation, or the function won't recalculate when the template is opened (unless force-coded to do so.)
This function has been tested with a variety of Excel versions, from Excel 97 through Excel 2010. It will work with Excel 97 or higher.
VBA Code Required:
The following should be copied into a standard code module:
Option Explicit Function GetName(Optional NameType As String) As String 'Author : Ken Puls (www.excelguru.ca) 'Function purpose: To return the following names: 'Defaults to MS Office username if no parameter entered 'Formula should be entered as =GetName([param]) 'For Name of Type Enter Text OR Enter # 'MS Office User Name "Office" 1 (or leave blank) 'Windows User Name "Windows" 2 'Computer Name "Computer" 3 'Force application to recalculate when necessary. If this 'function is only called from other VBA procedures, this 'section can be eliminated. (Req'd for cell use) Application.Volatile 'Set value to Office if no parameter entered If Len(NameType) = 0 Then NameType = "OFFICE" 'Identify parameter, assign result to GetName, and return 'error if invalid Select Case UCase(NameType) Case Is = "OFFICE", "1" GetName = Application.UserName Exit Function Case Is = "WINDOWS", "2" GetName = Environ("UserName") Exit Function Case Is = "COMPUTER", "3" GetName = Environ("ComputerName") Exit Function Case Else GetName = CVErr(xlErrValue) End Select End Function
- The formula can be referenced in worksheet cells as shown in the example below:
- The formula can also be called in VBA in the same manner. For example:
- msgbox getname("windows") or msgbox getname(2) will return the Windows login name
- msgbox getname("computer") or msgbox getname(3) will return the Computer name
Be aware that this file does contain the full VBA code, so a macro warning will most likely present itself.
Also, Did you know?
This code has also been submitted to the VBAExpress Knowledge Base!