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.
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.
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.
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.
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.
Step 2: Click on the 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.
Step 4: The 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.
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.
Step 2: The MsgBox contains only the 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.
Step 2: The MsgBox contains both Ok and Cancel.
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.
Step 2: The MsgBox contains all three Abort, Retry and Ignore.
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.
Step 2: The MsgBox contains all three Yes, No, and Cancel.
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.
Step 2: The MsgBox contains both Yes and No.
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.
Step 2: The MsgBox contains both Retry and Cancel.
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.
Step 2: The message box with a critical icon appears.
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.
Step 2: The message box with a Question icon appears.
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.
Step 2: The message box with a ! (exclamation) icon appears.
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.
Step 2: The message box with an i(information) icon appears.
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.
Step 2: A dialogue box appears. Click on Yes.
Step 3: A dialogue box appears. Click Ok.
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.
Step 2: A dialogue box with the title "Be a geek" appears.
Similar Reads
VBA Strings in Excel In Excel's Visual Basic for Applications(VBA), strings are pivotal in handling and manipulating text-based data. Strings serve as a fundamental data type used to store a sequence of characters, enabling the representation of textual information, numbers, symbols, and more. Understanding how VBA hand
8 min read
Sub Procedure in Excel VBA A Sub Procedure is an action that is written in VBA code. It starts with a Sub statement and ends with an End Sub statement. It is used to manipulate the data in an Excel sheet which includes a table present in an Excel sheet or a pivot table present in the Excel sheet. The name of the procedure sho
3 min read
VBA Print Statement in Excel When there are many columns and rows in excel but we only want to save or print a few of them because that few rows and columns are important to us, at that case we will print them in Excel VBA so that we can fetch those details later and also to get the print out of that table because daily life ha
2 min read
Excel VBA | sum() functions Visual Basic for Applications (VBA) is the programming language of Excel and other offices. It is an event-driven programming language from Microsoft. With Excel VBA one can automate many tasks in excel and all other office software. It helps in generating reports, preparing various charts, graphs a
2 min read
InputBox Function in Excel VBA Input Box is one of the most frequently used functions in VBA Macro. The dialogue box that appears asks for input from the user and returns a constant/code/number/text. For example, if you want to create a log-in form in VBA, you will require an input box function. Let's learn how to create an input
11 min read