VBA Msg Box | How to Create a Excel VBA Message Box (Yes / No)?

Excel VBA MsgBox

When we start learning the very first thing we try to learn is VBA Message Box or MsgBox. MsgBox is nothing but a small box which holds the message to the user. In this tutorial, we are going to show you how to show message, how to customize message box, icon change, buttons change and other modalities of the message box.

MsgBox is part of the VBA in excel. If you are a new user of VBA nothing to worry we will introduce you to the VBA macro coding technique with MsgBox.

Firstly if you are not seeing Developer tab in your excel follow below steps to enable 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 Developer tab in your excel ribbon.

How to Create a VBA MsgBox?

Get started with creating a simple Excel VBA msgbox. We are going to display a simple excel VBA msgbox in the start-up.

Follow the below steps to create a simple excel vba msgbox.

Step 1: Go to the DEVELOPER tab and click on VISUAL BASIC.

Alternative: You can click the shortcut key ALT + F11.

Step 2: Now click on INSERT & insert MODULE.

Step 3: It will insert the new MODULE in your VBA editor.

Step 4: Start your macro by typing SUB which is the head of the macro. Give a MACRO a name and hit enter.

Step 5: 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.

Step 6: As soon as you see the word Msgbox press tab, it will start to show the Msgbox syntax.

Step 7: Now type the word Welcome to VBA in double quotes.

Step 8: We have created a simple excel vba msgbox macro. Now inside the macro place a cursor and press the key F5 which is the shortcut key to run a macro.

Step 9: As soon as you run the macro you can see the VBA msgbox in your excel window.

Structure of the Excel VBA Message Box

Now we know the method of showing a VBA msgbox. Now I will introduce you to the structure of the VBA Message Box. Message Box consists of four elements.

The Syntax of Excel VBA Message Box

Now we have seen the structure of the VBA MsgBox, each section of the VBA MsgBox is part of the syntax of the Excel VBA Message Box. As we type the word MsgBox we can see the syntax of the Excel VBA Message Box. Now I will introduce you to the MsgBox syntax.

Prompt: This is the first and mandatory argument of the Excel VBA MsgBox. 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 VBA MsgBox. For example, In our previous example, the default button showed as OK.

Title: This is the title of the VBA MsgBox. We can customize this title of the Excel VBA Message Box. We can 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 doubt 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.

Excel VBA MsgBox with 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 VBA MsgBox.

Step 1: First give Prompt i.e. message you want to show.

Step 2: Now the next argument is Buttons ignore this and jump to the next argument i.e. Title.

Step 3: We are done. Now run the macro by using 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 of the message box.

Excel VBA Message Box 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 Excel VBA Message Box button options including Yes/No . You can select any one of the available 20 message box buttons. For example, I have selected vbYesNoCancel.

Now run the macro we will see different message box button instead default OK button.

Excel VBA Message Box 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

Code:

Sub SerialNumber ()
MsgBox “Welcome to VBA”, vbYesNoCancel + VbQuestion,”Introduction to VBA”
End Sub

Result:

#2 – vbCritical Marks Icon

Code:

Sub MessageBoxExample ()
MsgBox “Welcome to VBA”, vbYesNoCancel + VbCritical, “Introduction to VBA”
End Sub

Result:

#3 – vbExclamation Marks Icon

Code:

Sub MessageBoxExample ()
MsgBox “Welcome to VBA”, vbYesNoCancel + VbExclamation, “Introduction to VBA”
End Sub

Result:

#4 – vbInformation Marks Icon

Code:

Sub MessageBoxExample ()
MsgBox “Welcome to VBA”, vbYesNoCancel + VbInformation, “Introduction to VBA”
End Sub

Result:

You can download the VBA MsgBox Excel Template here:- VBA MSG Box Excel Template

Things to Remember About VBA Message Box

We can combine both button and icon by combining with a plus (+) icon.
We combine many buttons by combining with a plus (+) icon.
As we progress to the next levels of VBA we will see the advanced message box options.
All the numerical prompt should be supplied with double quotes and numerical values do not require double quotes.

VBA MsgBox