Uh-oh! It’s almost Christmas and you haven’t mailed any greeting cards yet. Don’t worry, you can create a personalized Excel message box instead! That will warm your co-workers’ hearts, and it saves paper and postage costs too.
Get the Application UserName
In Excel VBA, you can use Application.UserName to get the registration name for the Microsoft Office applications.
However, some people don’t update that setting, and it might show a company name, instead of the user’s name.
Get the Network UserName
Another option is to get the network user name, with a Windows API call. There is sample code in the Microsoft Knowledgebase: Visual Basic Procedure to Get Current User Name, and I’ve used a variation on that code in my sample file.
You can find other API code examples on the VB.Net site, and API code on The Access Web, to pull more information, such as Computer name, or locale settings.
In the sample code shown below, the network username is pulled from the DLL. This code is stored in a regular code module.
'================================== ' Access the GetUserNameA function _ ' in advapi32.dll and ' call the function GetUserName. Declare Function GetUserName _ Lib "advapi32.dll" _ Alias "GetUserNameA" _ (ByVal lpBuffer As String, _ nSize As Long) As Long '================================== ' Main routine to Dimension variables, ' retrieve user name ' and display answer. Function Get_User_Name() As String ' Dimension variables Dim lpBuff As String * 25 Dim ret As Long, UserName As String ' Get the user name minus any ' trailing spaces found in the name. ret = GetUserName(lpBuff, 25) Get_User_Name = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1) End Function '==================================
Create the Personalized Excel Message
Next, you can create an Excel message box, and show the network username in the message text. In this example, I’ve also calculated the year for the current date. Then, add 1 to the year number, to offer best wishes for the upcoming year.
'================================== Sub ChristmasMessage() ' show greeting with username and ' upcoming year MsgBox " Merry Christmas, " _ & Get_User_Name & " " _ & vbCrLf _ & " and best wishes for " _ & Year(Date) + 1 & "! " End Sub '==================================
Download the Sample File
You can visit the Contextures website, to download the personalized Excel Message Box sample file. The file is in Excel 2007/2010 format, and is zipped.
In the sample workbook, the Workbook_Open event runs this macro, so the personalized message appears when the file is opened, if macros are enabled.
Watch the Video
To see the steps for adding the GetUserName code to your workbook, and creating a personalized Excel message, you can watch this short Excel video tutorial.
Or watch on YouTube: Excel Message with User Name