Home and Learn - Free Excel VBA Course

7.3 More about the Excel VBA Message Box

You have been using the MsgBox function quite a lot so far. But there's more to it than the simple versions we've used. Our message boxes have looked like this:

MsgBox("Number Required")

In between the round brackets of MsgBox we have typed a message in double quotes. There are, however, other options (arguments) you can add. Each of these options goes after a comma.

The first option is always the message itself. But as a second option, you can add a button argument. The button argument is what kind of buttons you want on your message box, along with an optional icon to go with them. For example, if you wanted OK and Cancel buttons with an information symbol, the code would be this:

Response = MsgBox("Number Required", vbOKCancel + vbInformation)

The message box itself would then look like this:

An OK/Cancel message box

You may have noticed that this message box has a Response = at the start. That's because MsgBox is actually a function (you'll learn about functions in the next lesson). The Response part is a variable name we made up, and could have been almost anything. A variable name then an equal sign are needed for more complex message boxes because they return a value. The message box above needs to return a value of which of the buttons was clicked, OK or Cancel. You can then test the value that's in the variable, which is the button the user clicked. More on that in moment.

But as soon as you type the comma after your message in double quotes you should see a list appear. This one:

A list of message box constants in Excel VBA

The buttons on the list are:

vbAbortRetryIgnore
vbOKCancel
vbOKOnly
vbRetryCancel
vbYesNo
vbYesNoCancel

The symbols are:

vbCritical
vbExclamation
vnInformation
vbQuestion

The type of button you need and the symbol (if you want one) are separated by a plus sign (+).

As well as the message, and the buttons and symbols, you can add a title to the top of your message boxes. The title will replace the default Microsoft Excel, and needs to go between double quotes:

Response = MsgBox("Number Required", vbOKCancel + vbCritical, "Exiting Sub")

The above message box would then look like this:

A message box with a Tiltle set

You don't have to do anything with the value that the message box returns, but you can if you need to. In the code below, we're using an If Statement to test what is inside the Response variable:

Response = MsgBox("Number Required", vbOKCancel + vbCritical, "Exiting Sub")

If Response = vbOK Then

MsgBox ("OK")

ElseIf Response = vbCancel Then

MsgBox ("Cancel")

End If

The two buttons we've used are OK and Cancel. If the users clicked the OK button then the value that the message box stores in the Response variable is 1. This 1 is hidden behind the inbuilt constant vbOK, which you can use instead of typing 1. Using the constant instead of the numeric values makes your code easier to read.

However, if the user clicked Cancel then the value will be 2, or vbCancel. Here are all the constants and the numeric values that they hide:

Table showing message box button values in VBA

 

In the next lesson, we'll move on and study Functions.

< Sub Values

Next Lesson: 7.4 Functions >