VBA Message Box

Excel VBA Message Box

As there are two things in this word one is VBA and other is Message Box. In this, I’ll be explaining how to use Message Box function using VBA (Visual Basic for Applications).

VBA – It’s a programming language for those who work in Excel and other Office programs, so one can automate tasks in Excel by writing so-called Macros.

Message Box – As the name suggests it is nothing but a dialog box which is used to notify the user about something or giving some information, showing a custom message with some custom buttons like Yes/No or Cancel/Ok.

Message Box has various parts:

Title – It will display the Title of the Message box i.e. what the message box is about. By default, it will show the name of the application “Microsoft Excel” because we are opening it in Excel.

Prompt – It shows the message that we want to display in the dialog box.

Button – We can add and customize different buttons in the message box. Buttons we can add like Cancel, Ignore, Abort, Ok, Retry, etc. OK is a default button which is displayed.

Close Icon – It has a red color close icon from where we can directly switch off the Message Box.

As we know that MsgBox is a function in VBA like other functions it also has syntax and arguments to be passed in it.

MsgBox( prompt [, buttons ] [, title ] [, helpfile, context ] )

Syntax of VBA Message Box in Excel

The syntax for VBA Message Box is as follows:

Prompt – As we have discussed earlier that Prompt is a required argument. It displays the message that we see in the MsgBox. Our example text is “ This is Message Box Demo…” is the Prompt. We can use 1024 characters in a prompt and can also be used to display the values of the variables.

Buttons – It displays the buttons in the box. Suppose if I use vbOKCancel it will display both OK and Cancel button,  and if I use vbOKOnly it will display only OK button.

Helpfile – It opens the help file when the user clicks on the Help button. The Help button will only appear if we write the code for it. We also need to specify the context arguments, while using helpfile.

Button Constants in MsgBox

In this section we are going to cover the different buttons used in the Message Box:-

• vbOKOnly – This only shows OK button.
• vbOKCancel – This shows OK and Cancel button.
• vbYesNo –  This shows Yes/ No buttons.
• vbYesNoCancel –  This shows Yes, No, and Cancel buttons.
• vbAbortRetryIgnore – This shows Abort, Retry and Ignore Buttons.
• vbRetryCancel – This shows Retry and Cancel button.
• vbMsgBoxHelpButton – This shows the Help button.

Now we will see how the Message Box is created in the VBA.

To enable Macro’s in Excel you have to first enable the Developers Tab and then change the settings through which Macro’s run.

Creating Message Box VBA Code in Excel

Below are the different examples of Message Box VBA code in Excel. Let’s understand this with the help of an example.

Example #1

Step 1: Open Developer Tab, select the Visual Basic Editor to form the Code group or you can also press F11 to open it.

Step 2: After pressing F11, Microsoft VBA (Visual Basic for Applications) windows appears.

Code:

Sub Messagebox()

End Sub

From above we can see that the Macro code which we are writing will work only with the sheet we are working with i.e “vba msgbox.xlsm”

Step 3: Now we will type the code for MsgBox to display “This is Message box Demo…!”. The code for it is.

Code:

Sub Messagebox()

msgbox "This is a Message Box Demo...! "

End Sub

Step 4: Now we will run the following code and by clicking on RUN or by pressing on F5. As we can see that Message Box is displayed with the OK button and Message.

 

Example #2

In this, we are going to display the Message with Icon constants, Yes, No, and Title. All the steps from 1-3 are the same we have to make changes in step 4.

Step 4:

Code:

Sub Messagebox()

msgbox "This file contains virus. Do you want to continue", vbYesNo + vbExclamation, "This is Title"

End Sub

Step 5: As we can see that message box is displayed with the with Icon constants (Exclamation mark), Yes, No, and Title.

Things to Remember

1. Message Box is used to display a message or warning on the screen similar to a dialog box.
2. In this “Prompt” argument is mandatory, all other arguments are Optional.
3. OK button is the default button.
4. VBA code is halted when the Message Box is displayed.

This is a guide to VBA Message Box. Here we discuss how to get Message Box in VBA Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA UBound Function
2. Excel CODE Function
3. Using the VBA END Function
4. COUNTA Function in Excel