VBA MsgBox Function

In VBA, the dialog box can be created by using the MsgBox function. The MsgBox enables creating dialog boxes with many options.

VBA message box featured image

To interact with the users of your application, you may create dialog boxes. In VBA, a dialog box can be created by using the MsgBox function.

  • The MsgBox enables the creation of 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 you want to quit?” with Yes and No options.
  • Similarly, before performing a critical operation like deleting records, a dialog box appears confirming the user with a message and OK/Cancel options.
  • You may set all these options (Yes/No, OK/Cancel, etc.) along with the 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:

Argument Description
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 small letters.
  • The carriage return character (Chr(13)) can be used for adding lines in the dialog box message.
Button This is an 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 that appears in the title bar of the message box. If not provided, the default is the application name.
helpfile  It is used to provide context-sensitive help. If you provide the helpfile then context is also required.
context  The context is 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:

Function MsgBox_exmample()

   'A simple message in dialog

   MsgBox ("Welcome to the VBA World!")

End Function

VBA msgbox

As no button value is specified, 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 a simple message. No other action is performed in this example as you click Yes or No.

Function MsgBox_exmample()

   'A Demo of Yes and No

   a = MsgBox("Dialog with Yes/No!", 4)

End Function

msgbox Yes 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:

Function MsgBox_exmample()
   'buttn value = 3 with Yes/No/Cancel
   numa = MsgBox("Yes/No and Cancel options!", 3, "Yes, No and Cancel Demo")

   If numa = 6 Then
      MsgBox "Yes option pressed!"
   ElseIf numa = 7 Then
      MsgBox "No option pressed!"
   Else
      MsgBox "Cancel pressed!"
   End If

End Function

msgbox Yes No cancel

In the demo, you can see another simple dialog appear 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 a 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 a numeric value.

Have a look at the code and output:

Function MsgBox_exmample()

   'buttn value = vbQuestion

   x = MsgBox("Demo of multiline" & vbNewLine & "message box" & vbNewLine & "Three Lines", vbQuestion, "Multi-Line Box")

  

End Function

msgbox multi line box

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

Using a combination of two buttons – changing the 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 the spacebar or escape key to close the dialog and see what the next dialog displays:

Function MsgBox_exmample()

   'button value = 3 + vbDefaultButton3
   numa = MsgBox("Press escape or space bar!", 3 + vbDefaultButton3, "Two buttons demo")

   If numa = 6 Then
      MsgBox "Yes pressed!"
   ElseIf numa = 7 Then
      MsgBox "No pressed!"
   Else
      MsgBox "Cancel pressed!"
   End If

End Function

msgbox multi buttons

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 the third button default. Similarly, the vbDefaultButton1, vbDefaultButton2, and vbDefaultButton4 make the 1, 2, and 4 buttons default, respectively.

Adding a Help button with a critical icon

This dialog is the combination of three buttons.

  • The vbYesNo displays two options: Yes and No.
  • The vbCritical is added for the critical sign with Windows sound.
  • The third button is a Help button by using the constant: vbMsgBoxHelpButton.
Function MsgBox_exmample()

   'A demo of showing help button with critcal icon

   numa = MsgBox("Are you sure deleting this account permanently?", vbYesNo + vbCritical + vbMsgBoxHelpButton, "Help button demo")

End Function

msgbox help

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:

Button Constant Value Description
VbOkOnly 0 This is the default and displays the OK button only.
vbOKCancel 1 For OK and Cancel buttons.
vbYesNoCancel 3 Displays Yes, No, and Cancel buttons.
vbYesNo 4 Displays Yes and No buttons.
vbAbortRetryIgnore 2 Displays Abort, Retry, and Ignore buttons.
vbRetryCancel 5 Displays Retry and Cancel buttons.
vbCritical 16 Displays a critical message icon.
vbQuestion 32 Displays a question mark icon.
vbExclamation 48 Displays an exclamation mark icon.
vbInformation 64 Displays an information icon.
vbDefaultButton1 0 Sets the first button as the default.
vbDefaultButton2 256 Sets the second button as the default.
vbDefaultButton3 512 Sets the third button as the default.
vbDefaultButton4 768 Sets the fourth button as the default.
vbApplicationModal 0 Modal to the current application only.
vbSystemModal 4096 Modal to all applications in the system.
vbMsgBoxHelpButton 16384 Adds a Help button to the message box.
VbMsgBoxSetForeground 65536 Brings the message box to the foreground.
vbMsgBoxRight 524288 Aligns the message box to the right side of the screen.
vbMsgBoxRtlReading 1048576 Enables right-to-left reading for the message box.

Table of returned values for different buttons

Button Returned Value
Ok 1
Cancel 2
Yes 6
No 7
Abort 3
Retry 4
Ignore 5