MsgBox Function in Excel VBA

The MsgBox function

The VBA MsgBox function is an easy way to display information and to take simple input (like Yes, No, Cancel) from the users.

MsgBox function: Syntax

A simplified version of the MsgBox syntax is as follows:

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

MsgBox function: Parameters or Arguments

The arguments are defined as follows:

  • prompt: It is required. This text is displayed in the message box.
  • buttons: It is optional. Here we shall use some code such as vbYesNo, vbOKOnly in this place. According to this code, different buttons will appear in the message box. For example, see this MsgBox function in the GetAnswer macro:
    Sub GetAnswer()
        Ans = MsgBox("Process the monthly report?", vbYesNo)
        If Ans = vbYes Then MsgBox ("Some reports will be showed :)")
        If Ans = vbNo Then Exit Sub
    End Sub

    This code (vbYesNo) will show two buttons in the message box, one Yes button and the other one is No button.

    Alternatives of UserForm in Excel: InputBox & MsgBox Functions

    Yes No Button showed in the dialog box due to code vbYesNo in the MsgBox function.

  • title: It is optional. This text appears in the message box’s title bar.

MsgBox function: Example

MsgBox function can be used by itself like this one: MsgBox “Click OK to continue” (parenthesis not included in this way) or it can be assigned to a variable like the above one: Ans = MsgBox(“Process the monthly report?”, vbYesNo).

In the following VBA macro, MsgBox function is assigned to a variable. Then the variable can be used in effective way.

Sub GetAnswer()
    Ans = MsgBox("Process the monthly report?", vbYesNo)
    If Ans = vbYes Then MsgBox ("Some reports will be showed :)")
    If Ans = vbNo Then Exit Sub
End Sub

The Ans variable will have two values corresponding to vbYes or vbNo. If value is vbYes, it will work one work and if value is vbNo then it will do different work.

The following Table lists the built-in constants that you can use for the buttons argument. You can display your preferred buttons, make visible an icon, and select which button will be default.

MsgBox function: Constants

MsgBox TABLE: Constants Used in the MsgBox Function

Constant Value Description
vbOKOnly 0 It displays OK button.
vbOKCancel 1 It displays OK and Cancel buttons.
vbAbortRetryIgnore 2 It displays Abort, Retry, and Ignore buttons.
vbYesNoCancel 3 It displays Yes, No, and Cancel buttons.
vbYesNo 4 It displays Yes and No buttons.
vbRetryCancel 5 It displays Retry and Cancel buttons.
vbCritical 16 It displays Critical Message icon.
vbQuestion 32 It displays Query icon (a question mark).
VBExclamation 48 It displays Warning Message icon.
vbInformation 64 It displays Information Message icon.
vbDefaultButton1 0 First button is default.
vbDefaultButton2 256 Second button is default.
vbDefaultButton3 512 Third button is default.

Observe the following VBA macro. I have used a combination of constants (vbYesNo, vbQuestion and vbDefaultButton2) to show a dialog box with a Yes button, a No button, and a Question mark icon. Default button (vbDefaultButton2) will be the second button (No button).

Sub GetAnswer()
    Config = vbYesNo + vbQuestion + vbDefaultButton2
    Ans = MsgBox("Process the monthly report?", Config)
    If Ans = vbYes Then MsgBox "You pressed to see the Monthly Report."
    If Ans = vbNo Then Exit Sub
End Sub

See the following figure.

Alternatives of UserForm in Excel: InputBox & MsgBox Functions

Showing Yes, No buttons and a question mark beside the Text.

MsgBox function: Download

Download this file to practice yourself.

Happy Excelling ☕

Kawser on EmailKawser on FacebookKawser on LinkedinKawser on TwitterKawser on Youtube

Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply.

Keep in mind this African proverb:

"If you want to go fast, go alone,
If you want to go far, go together."

Let's together explore Excel deeply!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.