VBA MsgBox

What Is VBA MsgBox In Excel?

VBA MsgBox is a simple pop-up window that is often used to show a simple message to the user. We can use the MsgBox in two ways:

1. To inform the user about the specific action or what they need to do next.
2. To get the input from the user for further action, let’s say YES or NO or CANCEL.

When the message box is showing the message Excel and macro running will halt until we give some input. An input could be based on the design of the message box.

Anatomy Of A VBA MsgBox

Anatomy is a science word, but we need to use this word to explain the body structure of the VBA message box.

Following is the structure of the VBA Message Box.

1. Title: This is used to show the title of the message box. A title can be modified according to the requirement of the user. Since we have not given any title by default it shows the application name i.e., “Microsoft Excel” in this example.

2. Prompt: This is the section that displays the message that we need to deliver to the users. We can design the message as per our requirement or we can show some data or tables as well.

3. Symbol: This is the icon we can design based on the message that we need to deliver to the users. In this example, we have used the Information icon, like this, we can design critical, important, or many other symbols.

4. Button (s): OK is the default button we get. The message box will disappear if we click on the OK button. We can design our own buttons like YES or NO, Cancel, Ignore, Retry, etc.

5. Cancel Button: We can close the message box by clicking on this icon.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Syntax Of The VBA MsgBox Function

The syntax of MsgBox is as follows –

Prompt: This is the mandatory argument of the MsgBox function. In the argument, we can design the message that we want to deliver to the end users. In the anatomy of the message box “How are you doing today?” is the prompt we have given.

We can deliver our message up to 1024 characters. In this argument, we can also display the values of variables, and also, we can show the message in multiple lines.

[Buttons]: This is an optional argument. By default, we will get the OK button. However, we can design buttons like OK & CANCEL, YES or NO, etc.,

Apart from this, we can also display message icons like Critical, Information, and Alert.

[Title]: We can design the title that we need to give to the message box. We will get the title at the top of the message box. Since this is an optional argument, we will get the application name Microsoft Excel by default if we ignore this argument.

[Helpfile]: Using this argument we can specify the help file for the users. This will help the user to locate the help file by clicking on the help button. If we specify this, it is mandatory to specify the [context] number.

[Context]: This is a numerical expression, that is the help context number assigned to the appropriate help topic.

Note: We rarely use [helpfile] and [context] arguments, so let’s not bother about them too much.

Only the Prompt argument is mandatory in the message box function and all others are not mandatory.

Excel VBA MsgBox Button Constants

Example #1 – OK As The Default Button

First, we will design the message box from the scratch.

Start the subroutine procedure by giving a name to the excel macro.

Enter the MsgBox function.

For the prompt argument of the message box, give the message in double quotes.

Ignore all the other arguments of the MsgBox function and the code reads like this.

Code:

Sub Message_Box()
MsgBox “Hello, How are you?”
End Sub

When we run this code, we will get the following message box.

As we can see we have got the OK button by default. We can also specify vbOkOnly in the button’s argument of the MsgBox function.

Example #2 – OK And CANCEL Button

If we want to show the OK and CANCEL buttons in the message box, we need to choose the vbOkCancel option in the Button argument of the MsgBox function.

Enter Ctrl + Space button to get the IntelliSense list.

Choose the vbOKCancel option and we will have the following code.

Sub Message_Box()
MsgBox “Hello, How are you?”, vbOKCancel
End Sub

When we run this code, we will get the following MsgBox.

Example #3 – YES Or NO Button

If we need to show YES or No button, then we need to choose the vbYesNo button option.

Sub MsgBox_vbYESNO ()
MsgBox “Hello, How are you?”, vbYesNo
End Sub

We will get the following buttons in the message box.

Example #4 – YES, NO, And CANCEL Button

To show Yes, No, and Cancel buttons we need to give vbYesNoCancel.

Sub MsgBox_vbYESNOCANCEL ()
MsgBox “Hello, How are you?”, vbYesNoCancel
End Sub

We will get the following message box.

Example #5 – RETRY And CANCEL Button

To show Retry and Cancel buttons we need to use vbRetryCancel.

Sub MsgBox_vbRetryCancel ()
MsgBox “Hello, How are you?”, vbRetryCancel
End Sub

We will get the following message box.

Example #6 – ABORT, RETRY, and IGNORE Buttons

If we want to show Abort, Retry, and Ignore buttons, we need to use vbAbortRetryIgnore.

Sub MsgBox_vbRetryCancel ()
MsgBox “Hello, How are you?”, vbRetryCancel
End Sub

This will display the following message box.

Example #7 – Show Multiple Buttons

We can show more than one constant button in the message box. For example, if we want to show YES, NO along with the HELP button, then, we need to write a code like the following.

Sub MsgBox_vbRetryCancel()
MsgBox “Hello, How are you?”, vbYesNo + vbMsgBoxHelpButton
End Sub

We have given two constant buttons by adding the plus symbol.

We will get the following message box.

In this way, by adding a plus symbol, we can show multiple buttons.

Example #8 – Setting a Default Button

If we look at all the previous codes, we have got the first button as the default button i.e., the first button is highlighted.

For example, look at the following message box.

In the previous message box, YES is the default button. However, we can change this to no by adding the default button as 2.

Sub MsgBox_vbYESNO_Default ()
MsgBox “Hello, How are you?”, vbYesNo + vbDefaultButton2
End Sub

In the above code, we have added the constant vbDefaultButton2 i.e., it will make the second button of the message box default like the following.

Similarly, when we have more buttons, we can use the default constant 1, 2, 3, and 4 to make them default buttons.

Excel VBA MsgBox Icon Constants

Example #1 – Show Information Icon

Apart from showing the buttons, we can also show icons to indicate the message that we are conveying. For example, if we are giving information to the users along with buttons, then we need to use the vbInformation constant.

Sub MsgBox_vbInforamtion ()
MsgBox “You will be redirected to next page”, vbOKCancel + vbInformation
End Sub

As we can see, we have added the vbInformation constant to the OK and Cancel buttons. We will get the following message box.

Example #2 – Critical Icon

If we want to show the critical icon, then we need to use the vbCritical constant.

Sub MsgBox_vbCritical()
MsgBox “You will be redirected to next page”, vbOKCancel + vbCritical
End Sub

This will show the critical icon.

Example #3 – Question Mark Icon

To show a question mark as an icon, we need to use the vbQuestion constant.

Sub MsgBox_VbQuestion()
MsgBox “Would you like to continue?”, vbYesNo + vbQuestion
End Sub

This will show the question mark icon.

Example #4 – Exclamation Icon

To show an exclamation as an icon, we need to use the vbExclamation constant.

Sub MsgBox_vbExclamation()
MsgBox “Would you like to continue?”, vbYesNo + vbExclamation
End Sub

This will show the question mark icon.

Customizing Title and Prompt in the MsgBox

VBA MsgBox by default takes the application name as Microsoft Excel if we do not specify any title. However, we can modify the title as per our needs.

Sub MsgBox_Title ()
MsgBox “Would you like to continue to the next stage?”, vbYesNo, “Stage 1 of 5”
End Sub

This will display the title in the message box like the below image.

Show two lines:

There are chances where we may need to show the message in multiple lines. For this, we need to combine the two line messages by using the vbNewLine along with the ampersand (&) symbol.

Sub MsgBox_Title ()
MsgBox “Would you like to continue to the next stage?” & _
vbNewLine & “Press Yes to Proceed”, vbYesNo, “Stage 1 of 5”
End Sub

This will show the message like the following.

To show the message in the second line, we have used the ampersand (&) symbol and then, we have used the vbNewLine to push the message to the second line.

Assigning MsgBox Value to a Variable

We can assign the variable value to a MsgBox and at the same time, we can assign the value of the message box to the variable.

For example, look at the following code.

Sub MsgBox_Variable ()
Dim k As String
k = “Hi, Good morning!!!”
MsgBox k
End Sub

We have defined the variable with a string data type.

Then, we assigned the value to the variable as Hi, Good Morning!!!

Similarly, for the MsgBox prompt, we entered the variable name k.

Now, the MsgBox should show the value given to the variable k.

Assign Value Given by the MsgBox to the Variable

When the user is asked to choose the options via buttons, we can perform a specific set of actions based on their input.

For example, assume we need to get the input from the user to enter a value in cell A1 if they click on YES, or else we need not have to enter any value.

The following code is the demonstration of the same.

Sub MsgBox_Variable_Input ()
Dim User_Input As String
User_Input = MsgBox(“Would you like to enter the value ‘Hello’ in cell A1?”, vbYesNo)
Range(“A1”).Value = User_Input
End Sub

Here, we have defined the variable User_Input with the string data type.

Then, for this variable, we are storing the value given by the message box i.e., Either Yes or No.

Next, we are storing the variable value in cell A1.

When we run this code, we will get the following result in cell A1.

We clicked on YES and we are supposed to get a YES value in cell A1, but we have got 6 instead.

The reason for this is, that vb constants will return numerical values, not the text values. The following table shows the constant and numerical values they return.

vb Constant	Button Name	Value
vbOk	Ok	1
vbCancel	Cancel	2
vbAbort	Abort	3
vbRetry	Retry	4
vbIgnore	Ignore	5
vbYes	Yes	6
vbNo	No	7

Since we clicked on YES, we have got the value as 6 instead of YES.

Perform Action Based on User Input: The following code will ask the user when they need to enter the value Hello in cell A1 or not. Users are required to click on YES if they want to or else NO.

Based on the user selection from the button, we will perform the action of inserting or not inserting a value in cell A1.

Sub MsgBox_Variable_Input ()
Dim User_Input As String
User_Input = MsgBox(“Would you like to enter the value ‘Hello’ in cell A1?”, vbYesNo)
If User_Input = vbYes Then
Range(“A1”).Value = “Hello”
Else
MsgBox “You clicked on No or cancel”
End If
End Sub

When we run this code, we will get the following message box.

If we click on YES we will get the Hello value in cell A1.

If we click on NO, we will get the following message box.

Important Things to Note

• MsgBox is a function in VBA.
• Only a Prompt argument is mandatory, and all others are optional arguments in VBA MsgBox.
• By default, we will get the OK constant button in VBA MsgBox.
• When we assign a value of the MsgBox we need to give values inside the brackets.
• The variable data type should be the same as the output of the message box.
• We can combine two or more buttons by adding the + symbol and both buttons and icons together.
• VBA MsgBox buttons will return numerical values as result.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA MsgBox, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA MsgBox. Here we explain how to use MsgBox button constants and icon constants in Excel VBA with examples and downloadable template. You may learn more from the following articles –

• VBA Screen Update
• VBA Const
• VBA GoTo STATEMENT