Excel VBA MsgBox
VBA MsgBox function is like an output function which is used to display the generalized message provided by the developer, it is like a print statement. This function or statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.
When we start learning VBA, the very first thing we try to learn is Message Box or MsgBox function. It is nothing but a small box that holds the message to the user. In this tutorial, we are going to show you how to show a message, how to customize message box, icon change, buttons change, and other modalities of the message box.
Enable Developer Tab
Firstly if you are not seeing the Developer tab in your excelDeveloper Tab In Your ExcelEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu., follow the below steps to enable the developer tab.
Step 1: Go to FILE > OPTIONS.
Step 2: Click on Customize Ribbon under Excel Options.
Step 3: On the right-hand side, check the box Developer to enable it.
Step 4: Click on Ok and close this window. Now you should see the Developer tab in your Excel ribbon.
MsgBox Function Syntax
As we type the word MsgBox in the module, we can see the syntax. Now I will introduce you to the syntax.
- Prompt: This is the first and mandatory argument. In the above example, I have to type the word “Welcome to VBA” as the Prompt. This is nothing but the message we need to show. We can show up to 1024 characters.
- Button: This is nothing but what kind of button we need to show on the MsgBox. For example, In our previous example, the default button showed as OK.
- Title: This is the title of the message box. We can customize this title and give our own name to the message box in double-quotes.
- [help file]: This is nothing but the help icon you want to show to the users. If they have doubts and if you have already specified the suggestions, then they can click on this hell file to learn more.
- [Context]: This is the numerical value assigned to the appropriate help topic.
How to Create a MessageBox using VBA Code?
Follow the below steps to create a simple msg box using the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task..
Step 1: Open Visual Basic Editor
Go to the DEVELOPER tab and click on VISUAL BASIC.
Alternative: You can click the shortcut key ALT + F11.
Step 2: Insert Module
Now click on INSERT & select MODULE.
It will insert the new MODULE in your VBA editorVBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software..
Step 3: Start Subprocedure
Start your excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. by typing SUB, which is the head of the macro. Give a MACRO a name and hit enter.
Step 4: Start Writing Code for MsgBox
- In between the Head & Tail of the macro, we need to write the code. So start the word Msgbox. It will display the MsgBox function.
- As soon as you see the word Msgbox press tab, it will start to show the Msgbox syntax.
- Now type the word Welcome to VBA in double-quotes.
- We have created a simple message box macro.
Step 5: Run the Code
- Now inside the macro, place, a cursor and press the key F5, which is the shortcut key to run a macro.
Step 6: Output
As soon as you run the macro, you can see the message box in your excel window.
Structure of Message Box
Now I will introduce you to the structure of the Message Box. It consists of four elements.
#1 – Title
By default message box displayed the title of the message box as Microsoft Excel. We can modify this as well. After the Prompt is supplied, we can give our own name to the MsgBox.
Step 1: First, give Prompt, i.e., the message you want to show.
Step 2: Now, the next argument is Buttons ignore this and jump to the next argument, t, i.e., Title.
Step 3: We are done. Now run the macro by using the F5 key.
Now you can see the difference in the title of the message box. Instead of the default title Microsoft Excel now it is showing Introduction to VBA as the title.
#2 – Buttons
We have learned how to show a message box, how to alter the message box title. Now we will see the option of the message box button.
Once the prompt input is supplied next thing in the syntax is the message box button.
We have as many as 20 kinds of Message Box button options, including Yes/No. You can select any one of the available 20 buttons. For example, I have selected vbYesNoCancel.
Now run the macro, we will see different message box buttons instead of the default OK button.
#3 – Combination of Icon & Buttons
We have seen how to modify message box buttons. Along with the message box button, we can show icons as well, like the below image.
There are four kinds of icons available. vbCritical, vbQuestion, vbExclamation, and vbInformation.
In order to show the button along with the icon, we need to combine both by entering the plus (+) icon together.
#1 – vbQuestion Marks Icon
MsgBox “Welcome to VBA”, vbYesNoCancel + VbQuestion,”Introduction to VBA”
#2 – vbCritical Marks Icon
MsgBox “Welcome to VBA”, vbYesNoCancel + VbCritical, “Introduction to VBA”
#3 – vbExclamation Marks Icon
MsgBox “Welcome to VBA”, vbYesNoCancel + VbExclamation, “Introduction to VBA”
#4 – vbInformation Marks Icon
MsgBox “Welcome to VBA”, vbYesNoCancel + VbInformation, “Introduction to VBA”
Things to Remember
- We can combine both button and icon by combining it with a plus (+) icon.
- We combine many buttons by combining them with a plus (+) icon.
- As we progress to the next levels of VBA, we will see the advanced message box options.
- All the numerical prompts should be supplied with double quotes, and numerical values do not require double-quotes.
This has been a guide to VBA MsgBox Function. Here we learned how to create a message box and customize it along with other modalities using VBA examples and a downloadable excel template. You may also have a look at the following articles –