Show Personalized Excel Message Box

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.

excelmessageusername01

Get the Application UserName

In Excel VBA, you can use Application.UserName to get the registration name for the Microsoft Office applications.

excelmessageusername02a

However, some people don’t update that setting, and it might show a company name, instead of the user’s name.

excelmessageusername02

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

__________

You may also like...

2 Responses

  1. Gerald Strever says:

    Hi Debra,
    Great stuff! However the link to download the sample file is “dead” – i.e. Gives me the following Message: “The page cannot be found”.
    Best regards
    Gerald Strever

Leave a Reply

Your email address will not be published. Required fields are marked *