VBA Message Box

Excel VBA Message Box

There are two things in this word: VBA and the other is Message Box. In this, I’ll be explaining how to use the 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 to automate tasks in Excel by writing so-called Macros.

Message Box – As the name suggests, it is nothing but a dialog box used to notify the user about something or give 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, MsgBox is a VBA function 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 the OK and Cancel buttons,  and if I use vbOKOnly, it will display only the 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 the help file.

Button Constants in MsgBox

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

• vbOKOnly – This only shows the OK button.
• vbOKCancel – This shows the OK and Cancel buttons.
• 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 buttons.
• 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 the 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 by clicking on RUN or 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. A message Box is used to display a message or warning on the screen, similar to a dialog box.
2. 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, practical examples, and a downloadable excel template. You can also go through our other suggested articles –

1. VBA UBound
2. VBA Get Cell Value
3. VBA END
4. VBA RGB