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.
- 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, the MsgBox function is assigned to a variable. Then the variable can be used in an 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 the value is vbYes, it will work one work and if the 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 the default.
MsgBox function: Constants
MsgBox TABLE: Constants Used in the MsgBox Function
Constant | Value | Description |
---|---|---|
vbOKOnly | 0 | It displays the 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 the Critical Message icon. |
vbQuestion | 32 | It displays a Query icon (a question mark). |
VBExclamation | 48 | It displays the Warning Message icon. |
vbInformation | 64 | It displays the Information Message icon. |
vbDefaultButton1 | 0 | The first button is the default. |
vbDefaultButton2 | 256 | The second button is the default. |
vbDefaultButton3 | 512 | The third button is the 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. The 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.
MsgBox function: Download
Download this file to practice yourself.
MsgBox-function.xlsm
Happy Excelling ☕