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.

Advertisements

Posted on August 9, 2012, in VBA and tagged , , , , , . Bookmark the permalink. 5 Comments.

  1. Hey there! This is my first comment here so I just
    wanted to give a quick shout out and say I genuinely enjoy reading your posts.
    Can you recommend any other blogs/websites/forums that
    cover the same subjects? Thanks for your time!

    • Some good places to check out are: Chandoo.org, , tom urtis (atlas consulting) , excel guru.ca (ken Puls) , peltiertech.com (for all things charts). They all have slightly different content and between them you should find some useful stuff.

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: