Excel VBA MsgBox Explained

Show informative, easy to interact with messages to your Excel VBA program users. All you need to know about the MsgBox function is right here. Read on…

User interaction is a big deal

Why?

Because this is where your program meets the psyche, mindset, values, thoughts and habits of the user. Can you guess what those are?

If the user doesn’t feel “at home” with your application, he would not want to use it. The American science fiction writer, Larry Niven, captured the importance of the user experience beautifully:

“That’s the thing about people who think they hate computers. What they really hate is lousy programmers.”

While user experience (UX) deserves a whole book of its own, in this Blog post I’ll start with one of the simplest user interaction tools available for us in Excel VBA: the MsgBox function.

The Excel VBA MsgBox Function

As I pointed out in my Blog post about calling external Windows functions (DLL) from Excel VBA, as Excel is running on a Windows machine (Mac users, please excuse me here for a second), we can make use of the many libraries offered to us by Windows (and others).

You have probably seen a message box pop up in many other software packages running on Windows. Now you will see how the programmers of that software did it, and you will do it too!

The anatomy of a Windows message box is shown in the following image.

As you can see, there are several aspects of the message box we should have control over, when we call the MsgBox function to display a message to the user:

1. The Window title
2. The message text
3. The icon displayed next to the message
4. The buttons made available to the user
5. The default button (responding to the Enter key press on the keyboard)

In addition, we can also associate a help file with the message box, allowing the user to open a help window with our explanations. However, the supported help file format (HLP) is very outdated and this feature is not to be relied on anyway, so I never really use it.

As the MsgBox is serviced by Windows, some aspects of the message box are coming from your Windows settings, such as the font size, Window colors (background, title, frame) and position. Some of these are changeable in your Windows Display and Appearance setting.

Calling the MsgBox Function in Excel VBA

The MsgBx function returns a numeric code representing the button action clicked to close the message box. For example, clicking the “OK” button will return 1. Clicking “Retry” will return 4.

The function expects at least one argument: the message text to show to the user. If that’s the only argument supplied, the message box title will be “Microsoft Excel”, there will be no icon displayed and there will be a single “OK” button for the user to close the dialog box with.

As you already know that user experience is important, I strongly advise to always provide a title, an icon and buttons to be placed on your messages.

Here’s the function stub (arguments in square brackets are optional):

MsgBox (message, [ buttons, ] [ title, ] [ helpfile, context ])

Here’s an example:

Dim intMsgResponse as Integer

intMsgRespone = MsgBox("Your programming skills are improving!", vbExclamation, "Skills Report")

You probably noticed that I haven’t specified the buttons to show. This is because the default is all we need in this example: just the “OK” button.

By the way, if you don’t need to check the user response, you can call the function as if it was a Sub, like that:

MsgBox "Your programming skills are improving!", vbExclamation, "Skills Report"

Let’s see another example:

intMsgRespone = MsgBox("Have you completed the Excel VBA course yet?", vbQuestion + vbYesNo, "Skills Report")

Here we are asking a Yes/No question, therefore we chose the question icon and the YesNo buttons pair. We can check the value of the returned code in intMsgResponse to see what the user have clicked: yes (6) or no (7).

Your eye may have been sharp enough to notice that the icon code and the buttons code are entangled together into a single argument passed to the function, using the add operator. I promise to explain how that works in a future Blog post.

The default button here is the first button: “Yes”. If we want to “No” button to be set as the default, we can add another number to the mix, using the appropriate enumerator, vbDefaultButton2 in our case:

intMsgRespone = MsgBox("Have you completed the Excel VBA course yet?", vbQuestion + vbYesNo + vbDefaultButton2, "Skills Report")

Thanks to the helpful Intelligence feature of the VBA Editor, you will be offered all the available enumerators as you start typing (“vb…”) in the buttons argument position. You can find a complete list here.

Speaking of enumerators, watch out for a future Blog post on enumerators in which I will explain what they are and show you how to create your own enumerators in your program.

If you found this Blog post helpful, do share it with others – they will thank you!