Monthly Archives: August 2012

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

Excel VBA Message Boxes

Message boxes can be useful little things when running VBA code. They can be a simple message to let users know something has happened or allows them to make choices as the code runs.

Why the need to let people know something has run? Surely they are not that stupid? Well….you’d be surprised. Code will run very quickly and anyone not familiar with VBA may not notice something has run in the background so will press the button over and over again and before you know it you have 10 copies of the same workbook! So avoid this sort of problem and let people know what has happened.

So how do you set up a message box?

At its most basic all you need is this;

Sub ShowMessage ()

MsgBox “Hello”

End Sub

Image

By default you get the OK button to close the message box. So if all you need is a simple message to pop up then that is all you need: MsgBox “prompt or message to the user”, but you can do a lot more…

There are 5 things you can set to customise your message box a bit:

  • Message box icon
  • Buttons displayed
  • Which button is the default one
  • Modality (I’ll explain in a bit what that is)
  • Custom title

Message box icons:

Image

I’ve left the message in view so you can see where the icon appears

Button choices:

Image

In each case note that the default button is always the first one on the left (bold outline).
To make buttons respond differently you need to write code to respond to the choice of button. I’ll write something on that in a separate blog.

Setting the default button:

Image

There is also a button4 option but this is used if you add an extra help button

Modality:

This basically how you want the message box to behave.

Image

Putting it together….

After typing in your message or prompt to the user type in a COMMA and this will bring up all the options for the second parameter. Select as many as you need, with a ‘+’ symbol between each one. If you type in another comma, you will move on to the next parameter which is the message box title. A completed example might look like this;

Sub ShowCustomMessage ()

MsgBox “Hello”, vbInformation + vbYesNo + vbDefaultButton2 + vbApplicationModal

End Sub

This would give us:

Image

Remember that all these are optional parameters, so you can select as many or as few as you like.

Custom title:

Once you have selected all your options, type in a COMMA and this will give you the option to customise the message box title, replacing the standard Microsoft Excel text you normally see.

Sub ShowCustomMessage ()

MsgBox “Hello”, vbInformation + vbYesNo + vbDefaultButton2 + vbApplicationModal,”Greetings”

End Sub

And this would give us;

Image

So that is how to set up a message box using VBA. In the next blog I’ll show how to customise the text that appears including custom data formats and line breaks.