Open In App

MessageBox in Excel VBA

Improve
Improve
Like Article
Like
Save
Share
Report

Message Box is one of the most frequently used functions in VBA Macro. The message box is a dialogue box that appears on the screen as an extra window. The dialogue box that appeared asks input from the user and returns a constant/code/number accordingly or it can use to display messages. In this article, we will learn how to create different types of message boxes in VBA Macro. 

Message Box 

A message box is a dialogue box that displays information that can be text, date, number, etc. like as shown in the below image. In Excel, we can easily create a message box using VBA.

Message-box

Parts of a message box: 

1. Title: The title of a message box specifies what properties or functions will this dialogue box performs. While writing a message box in VBA macro it is not mandatory to write a title. A title does not have multiple lines. If a title is not mentioned then by default the title becomes Microsoft Excel

Title-of-message-box

2. Prompt: The prompt is the heart of the message box. It specifies the work to be done or the information provided in a detailed manner. While writing a message box function it is mandatory to write a prompt statement. You can also display tables, dates, and numbers in the prompt. You can also add multiple lines in the prompt area. 

Prompt-text

3. Buttons: While working with basic Excel, you might have configured the different buttons that appear in a dialogue box. For example, Yes/No, Retry, Help, Abort/Retry/Ignore, etc. You can create and use any of the buttons, according to your requirement. While writing a message box function it is not mandatory to specify the buttons. By default, Ok appears. 

Buttons-in-message-box

Creating a Basic Message Box in VBA

In Excel, we can easily create a message box with the help of the MsgBox function. 

Syntax:

Msgbox “prompt(mandatory)”, buttons(temporary), “title(temporary)”, “helpfile(temporary)”, “context(temporary)”

Here, 

  • Prompt: Prompt is the detailed information of the MsgBox. It is mandatory to add a prompt while writing the MsgBox function. You can also add multiple lines to the prompt.
  • Buttons: It is the temporary argument that you wish to add. By default, it shows only the Ok button. To add custom buttons, you have the code for each button. For example, vbRetryCancel, vbOkOnly.
  • Title: It is the temporary argument of the MsgBox function. You cannot add multiple titles to a message box.
  • HelpFile: This is a very rare argument that is passed on while creating a MsgBox. This argument is only provided when you are using a help button. When you are passing the help file, you have to use context arguments also.
  • Context: It is only used with the help file and help button. You need to have a numeric help context number to the appropriate help topic.

Follow the below steps:

Step 1: The MsgBox keyword is used to create a message box is MsgBox. So write MsgBox. Start with a double quote and write the prompt you want to display. Press Enter. 

Creating-message-box

Step 2: Click on the Run button. 

Click-on-run-button

Step 3: The Message Box appears. Here, the VBA code has stopped running and will not get executed until we select any of the buttons in the message box. Click Ok

 Message-Box-appears

Step 4: The VBA code resumes. 

VBA-code-resumes

Buttons in MessageBox

When you have typed a prompt in the MsgBox function. The next argument is separated with a comma(,). Now, the VBA editor gives us a list of buttons that can be added to the MsgBox function. You can select and add the button according to your requirements.  

Lists-of-buttons

The types of buttons in the MsgBox.

1. vbOkOnly(Default): This is the default button that appears. To create this button follow the following steps: 

Step 1: Write the code. vbOkOnly is the code that helps create only the Ok button. 

Write-the-following-code

Step 2: The MsgBox contains only the Ok button. 

This message-box-contains-only-one-ok-button

2. vbOkCancel: vbOkCancel code tells the VBA compiler to show Ok and cancel buttons while displaying the MsgBox. To create this button follow the following steps: 

Step 1: Write the code. vbOkCancel is the code that helps create the Ok and Cancel buttons. 

Write-the-code

Step 2: The MsgBox contains both Ok and Cancel.  

Message-box-contains-two-buttons

3. vbAbortRetryIgnore: This code displays Abort, Retry and Ignore buttons in the message box. To create this button follow the following steps: 

Step 1: Write the code. vbAbortRetryIgnore is the code that helps create the Abort, Retry, and Ignore buttons. 

Creating-vbAbortRetryIgnore

Step 2: The MsgBox contains all three Abort, Retry and Ignore.

vbAbortRetryIgnore-is-created

4. vbYesNoCancel: vbYesNoCancel code displays Yes, No, and Cancel buttons in the message box. To create this button follow the following steps: 

Step 1: Write the code. vbYesNoCancel is the code that helps create the Yes, No, and Cancel.

Creating-vbYesNoCancel-buttons

Step 2: The MsgBox contains all three Yes, No, and Cancel.

vbYesNoCancel-is-created

5. vbYesNo: vbYesNo code displays Yes and No buttons in the message box. To create this button follow the following steps: 

Step 1: Write the code. vbYesNo is the code that helps create the Yes and No.

Creating-vbYesNo-buttons

Step 2: The MsgBox contains both Yes and No.

vbYesNo-is-created

6. vbRetryCancel: vbRetryCancel code displays Retry and Cancel buttons in the message box. To create this button follow the following steps: 

Step 1: Write the code. vbRetryCancel is the code that helps create the Retry and Cancel.

Creating-vbRetryCancel-button

Step 2: The MsgBox contains both Retry and Cancel.

vbRetryCancel-is-created

7. vbDefaultButton2: You can use any of the default buttons. There are four types of default buttons. vbDefaultButton1, vbDefaultButton2, vbDefaultButton3, vbDefaultButton4. Here, vbDefaultButton2 is the button that is selected by default when a Msgbox appears. If you press Enter, then it automatically gets selected. To create this button follow the following steps: 

Step 1: Write the code. Using vbAbortRetryIgnore creates three-button Abort, Retry and Ignore. vbDefaultButton2 is the code that helps us select the second default button i.e. Retry. 

Step 2: The MsgBox appears and the default is Retry

Different Icons in Excel VBA

While working with basic excel. You have come across different warnings and information. These all are represented with symbols. For example, a yellow exclamation for warning, a blue color question marks for information, etc. Here, we have discussed some of the icons that can appear in a message box. 

1. vbCritical: This icon is a red cross. This red cross is used to display a critical message. To create this icon follow the following steps: 

Step 1: Write the code. vbCritical creates a red cross. 

Creating-vbCritical

Step 2: The message box with a critical icon appears.

vbCritical-is-created

2. vbQuestion: This icon is a blue question mark. This blue question mark is used to display when you forgot something to choose in a procedure. To create this icon follow the following steps: 

Step 1: Write the code. vbQuestion creates a blue Question Mark.

Creating-vbQuestion-icon

Step 2: The message box with a Question icon appears.

vbQuestion-icon-is-created

3. vbExclamation: This icon is a yellow exclamation mark. It is used to represent an error when the entered data doesn’t match with the already present data. To create this icon follow the following steps: 

Step 1: Write the code. vbExclamation creates a yellow exclamation mark. 

Create-vbExclamation-icon

Step 2: The message box with a ! (exclamation) icon appears. 

vbExclamation-is-created

4. vbInformation: This icon is a blue I mark that represents information. To create this icon follow the following steps: 

Step 1: Write the code. vbInformation creates a blue I information mark.

vbInformation-is-creating

Step 2: The message box with an i(information) icon appears. 

vbInformation-is-created

Values returned by Buttons

Till now, you have studied how to create a message box and the different buttons associated with it. Now, we will learn how you can create nested message boxes and also with some conditions that are more practical in the real designing of a dialogue box. A message box returns a code or a constant according to the buttons applied. Some of the most commonly used constants and their return value are given in the table below. 

Constant Value
vbOKOnly 0
vbOKCancel 1
vbAbortRetryIgnore 2
vbYesNoCancel 3
vbYesNo 4
vbRetryCancel 5

Note: You can use values as well as constant interchangeably while comparing the boolean expressions.

For example, given Yes, No, and Cancel buttons. Display a message box accordingly. So to do this task follow the following steps:

Step 1: Write the code. The initial MsgBox will return a value and it will get stored in a variable name ans. If you click yes, then it will go inside the if condition else it will output the other one. Click on the Run button. 

Write-the-code

Step 2: A dialogue box appears. Click on Yes.

Click-on-yes-button

Step 3: A dialogue box appears. Click Ok

Click-on-Ok-button

Editing Title in a Msg Box

You can edit the title in a message box. By default, it shows Microsoft Excel as the title. In the MsgBox, function the third argument is for writing your custom title. It is a temporary argument. Follow are the following steps: 

Step 1: Write the code. The third argument is your custom title. 

Editing-the-title

Step 2: A dialogue box with the title “Be a geek” appears. 

Title-of-the-message-box-is-changed



Last Updated : 22 Mar, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads