The VBA MsgBox function

For displaying a dialog box for interacting with the user, you may create dialog boxes. In VBA, the dialog box can be created by using the MsgBox function.

The MsgBox enables creating dialog boxes with many options. For example, a dialog box is displayed before quitting the application. In that case, you may ask the user “Are you sure?” with Yes and No options.

Similarly, before performing a critical operation like deleting records, a dialog box appears confirming user with a message and OK/Cancel options.

You may set all these options (Yes/No, OK/Cancel etc.) along with message displayed to the user by using MsgBox function.

Syntax of MsgBox in VBA

For creating a dialog, the general syntax is:

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

These arguments for MsgBox are explained below:

• prompt – This is the only required argument. The prompt is a string that appears to the user. For example, “Are you Sure?”. You may display any message there as per the requirement. However, the length of the message is limited –around 1024 characters. The limit also depends on the character width e.g. capital letters may take more space than the small letters. The carriage return character (Chr(13)) can be used for adding lines in the dialog box message.
• Button – This is optional argument. If you do not provide a value, the default is taken, which is 0. That is, the dialog appears with ‘Ok’ button only.
• Title – This specifies the message appear in the title bar of the message box. If not provided, the default is the application name.
• helpfile – used to provide context-sensitive help. If you provide the helpfile then context is also required.
• context – The context in relation to the helpfile. This is a numeric expression.

An example of simple MsgBox VBA

The first example simply displays the message in a dialog box with ‘Ok’ button. For that, just enclose the message without specifying a button, so the default is taken. Have a look at the code:

As no button value is specified, so it took the 0 value which is just the ‘OK’ button.

A demo of message box with Yes/No buttons

The button value = 4 displays the button with Yes and No options in the message box. See the example and code below with a Yes/No alert with the simple message. No other action is performed in this example as you click yes or no.

The example of Yes/No/Cancel buttons with title

If you want to create a dialog with Yes, No and cancel options then use the button value = 3. See the following example where this value is used along with the title option. As mentioned earlier, the title will appear in the title bar of the message box:

In the demo, you can see another simple dialog appears with the message that which button was pressed. This is done by accessing the returned value of the button. Pressing the button returns the following values:

• If Yes is pressed, it returns 6.
• If Cancel is pressed, it returns 7.
• For Cancel, it returns 2.
• See the complete list in the bottom section.

The VBA If..ElseIf..Else statement is used to get the value and display the respective dialog box.

A demo of multi-line VBA message box

For this demo, a multiline message box is created by using vbNewLine. Apart from that, button constant vbQuestion is used rather than numeric value. Have a look at code and output:

Note: See the list of button values with constants below.

Using combination of two buttons – changing default button

In the Yes/No/Cancel example, you might notice there is a default button. The default button is pressed as you close the dialog using the space bar or escape key etc. If you try that example again, you will see the ‘Yes’ button is the default.

For changing this to some other button like No or Cancel, you may use the combination of two buttons by using a ‘+’ sign. Have a look at the code below and run this example, press spacebar or escape key for closing the dialog and see what next dialog displays:

You saw, the Cancel button became the default. This is done by using the following combination:

3 + vbDefaultButton3

The vbDefaultButton3 button is used to make third button as default. Similarly, the vbDefaultButton1, vbDefaultButton2, and vbDefaultButton4 make the 1, 2 and 4 buttons as default, respectively.

Adding Help button with critical icon

This dialog is the combination of three buttons. The vbYesNo displays two options: Yes and No. The vbCritical is added for critical sign with windows sound and the third button is a Help button by using the constant: vbMsgBoxHelpButton.

List of buttons with constants, values and description

Following is the button list that you may specify in the MsgBox with the value and a little description wherever required:

• VbOkOnly = 0

This is the default and display OK button only

• vbOKCancel = 1

For Ok and Cancel buttons

• vbYesNoCancel = 3
• vbYesNo = 4
• vbAbortRetryIgnore = 2
• vbRetryCancel = 5
• vbCritical = 16
• vbQuestion = 32

Question mark displays in the dialog.

• vbExclamation = 48

Exclamation icon displays with the message in dialog.

• vbInformation = 64
• vbDefaultButton1 = 0
• vbDefaultButton2 = 256
• vbDefaultButton3 = 512
• vbDefaultButton4 = 768
• vbApplicationModal = 0
• vbSystemModal = 4096

The user has to respond to this message box or it will appearing in all applications in the system. For example, even going to a browser, the user will see the message box.

• vbMsgBoxHelpButton = 16384
• VbMsgBoxSetForeground = 65536
• vbMsgBoxRight = 524288
• vbMsgBoxRtlReading = 1048576

List of returned values for different buttons

• The Ok button returns 1
• Cancel = 2
• Yes – 6
• No = 7
• Abort = 3
• Retry = 4
• Ignore = 5