Excel VBA Message Boxes (Part 2)

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”.

Sub PersonalGreeting()

MsgBox “Hello “ & Application.UserName, vbInformation, “Greetings Excel User”

End Sub

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;

Sub PersonalGreeting()

MsgBox “Hello “ & Application.UserName & vbCr & Date, vbInformation, “Greetings Excel User”

End Sub

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.

Sub PersonalGreeting()

MsgBox “Hello “ & Application.UserName & vbCr & “Today is “ &Format(Date,”dddd”) _
& vbCr & “Time logged in: “ & Time, vbInformation, “Greetings Excel User”

End Sub

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

Advertisements

Posted on August 20, 2012, in VBA and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: