Uh-oh! It's almost the holidays 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.
Here's what the personalized Excel message box looks like.
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.
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
Go to my Contextures website, to download the personalized Excel Message Box sample file.
The zipped file is in xlsm format, and contains macros.
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, watch this short Excel video tutorial.