MsgBox in Microsoft Excel VBA

1. MsgBox in VBA
2. Example 1: Yes/No MsgBox
3. Example 2: Warning MsgBox
4. Example 3: Assigning a MsgBox to a Variable
5. Conclusion

VBA, short for Visual Basic for Applications, is a programming language that provides developers with various functions and features while working with Microsoft Office applications.

In this article, we will learn about VBA’s MsgBox function.

MsgBox in VBA

The MsgBox is a function in VBA that displays a message box when the code is run. When the message box is displayed, the code is halted.

It then waits for input from the user and continues execution accordingly. You can customize the message in this dialogue box and make your code behave according to the option chosen by the user.

The syntax of the MsgBox function in VBA is below.

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

The MsgBox function takes several arguments, as shown above. These are explained below.

1. prompt: This argument refers to the String type expression displayed as the message in the dialogue box. There is a limit on the number of characters you can display in the prompt, i.e., approximately 1024 characters, depending on the space taken up by the characters.

   You can also use the prompt argument to display the variables’ values. You can also break the message into separate lines using a carriage return or linefeed character between the lines.

2. buttons: This argument has a numeric value. It is used to specify the type of buttons that will be displayed and the style of the icons. You can also choose the number of buttons on the MsgBox.

   The default value for the buttons argument is 0, which only shows an OK button. In this article, we will explain the different types of buttons and icons.

3. title: This refers to a String type argument that specifies the caption of the MsgBox displayed in the title bar. If this argument is not written, the application’s name is displayed here, e.g., Microsoft Excel.

4. helpfile: Here, you can specify a help file as a String expression which will assist the user when they click the Help button. This button is shown using its specific code that we will mention below.

   If you specify a helpfile argument, it becomes necessary to provide the context as well.

5. context: This is the Help context number that the Help author assigns to the Help topic. If you specify a context argument, it is necessary to provide the helpfile as well.

Note: Only the prompt argument is required. The rest are optional to specify.

VBA MsgBox Buttons

As mentioned above, the buttons argument of the MsgBox function can state the number and type of buttons displayed in the dialogue box. This is done by specifying the button constant or its corresponding value.

The common types of button settings are summarized below.

1. vbOKOnly: This button has the value 0. It is used to display only the OK button.
2. vbOKCancel: The value for this button is 1. It displays both the OK and Cancel buttons.
3. vbAbortRetryIgnore: It has the value 2. It shows the Abort, Retry, and Ignore buttons.
4. vbYesNoCancel: It has the value 3. It displays the Yes, No, and Cancel buttons.
5. vbYesNo: This is the button having value 4. It displays the Yes and No buttons.
6. vbRetryCancel: This button has the value 5. It displays the Retry and Cancel buttons.
7. vbDefaultButton1: The first button is made default. Changing the number changes the default button; for example, vbDefaultButton2 means the second button is the default. The value also changes for each.
8. vbMsgBoxHelpButton: Value for this is 16384. It displays the Help button. When you use this, you must specify the helpfile and context arguments, or it will not work.

VBA MsgBox Return Values

The MsgBox function returns an integer type value depending on the button pressed. There are 7 return value constants with the corresponding numeric value that is returned.

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

You can store these numeric values in variables and perform actions in your code accordingly.

VBA MsgBox Icons

You can customize the displayed icon in the VBA MsgBox. It can be of the following types:

1. vbCritical

   

2. vbQuestion

   

3. vbExclamation

   

4. vbInformation

   

Example 1: Yes/No MsgBox

Let us look at some sample code that will show you how to use the MsgBox function. We will give an example of the Yes/No MsgBox, which displays the Yes and No buttons.

The code for this is below.

Sub YesNoMsgBox()

MsgBox "Still Want To Continue?", vbQuestion + vbYesNo

End Sub

Example 2: Warning MsgBox

Let us look at an example of a MsgBox that displays a warning message with the options to Abort, Retry, or Ignore. It also shows the usage of vbNewLine and the title argument.

Sub WarningMsgBox()

MsgBox "An exception occured" & vbNewLine & "Choose what you want to do", vbExclamation + vbAbortRetryIgnore, "Exception"

End Sub

Example 3: Assigning a MsgBox to a Variable

In the above examples, pressing buttons did not do anything in particular. However, if you want to change the course of action of your code according to the button selected by the user, you need to save the MsgBox return value into a variable.

Let us see how we can do that using the code below.

Sub MsgBoxExample()

Dim result As Integer

result = MsgBox("Do you want to continue?", vbYesNo + vbQuestion)

If result = vbYes Then ' Or result == 6

MsgBox "You clicked Yes"

End If

If result = vbNo Then ' Or result == 7

MsgBox "You clicked No"

End If


End Sub

Running this macro displays the following MsgBox:

If we click on Yes, this MsgBox is displayed:

Similarly, clicking on No prompts the following MsgBox:

Conclusion

This sums up our discussion on the MsgBox function and the different ways to customize it using VBA. Keep learning!