The 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 go 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 (one line):

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

The message box itself would then look like this:

You may have noticed that this message box has a Response = at the start. The Response 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 the return a value. The message box above needs to return a value of which of the buttons were 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:

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 symbol (+).

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:

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:

Constant	Value
vbOK	1
vbCancel	2
vbAbort	3
vbRetry	4
vbIgnore	5
vbYes	6
vbNo	7