Excel VBA Message Boxes (Part 2)
Posted by excelmate
In my previous blog on message boxes, I showed how you can set the various options to customise the message box to set icons, number and type of buttons etc.
This time let’s look at how we can customise the message itself.
Let’s say you want the message to display the user’s name automatically rather than just saying “Hello…whoever you are”.
MsgBox “Hello “ & Application.UserName, vbInformation, “Greetings Excel User”
The Application.User extracts the user name from the options panel. To check this go to FILE, OPTIONS, GENERAL, User Name (Excel 2010).
This then gives us;
It’s then entirely up to you how much more information you want to add to the message box.
If you want to add the date for example then you can simply add & Date to your line of code. If you want to create multiple lines of text in the message box rather than have a very wide one you can use the vbCr code to add a carriage return. So our modified code might look like this;
MsgBox “Hello “ & Application.UserName & vbCr & Date, vbInformation, “Greetings Excel User”
Note you have to have ampersand (&) both before and after the vbCr code word.
Let’s make this a little more informative and format the date to show the day of the week and show the time of day that the person opened the workbook.
MsgBox “Hello “ & Application.UserName & vbCr & “Today is “ &Format(Date,”dddd”) _
& vbCr & “Time logged in: “ & Time, vbInformation, “Greetings Excel User”
To make the date appear as a day of week we use the Format function. The syntax for this is:
Format(what are you formatting , “how you want it formatted”)
In this example I am using the format “dddd” to give us he day of the week in full e.g. Tuesday
Also note the use of underscore (_) at the end of the first line of code. This is a continuation line which tells the VB editor that the next line of code is actually all part of the same command i.e. it is all part of the same message box code. Use these to break up long lines of code so you don’t have to continuously scroll across the page to view your code.
So the final message box would look like this:
Next time…..how to get those Yes/No/Cancel buttons to work properly.
Follow me on Twitter @excelmate