The MsgBox function in Excel VBA displays a message box to the user. You can use message box to inform your users by showing a custom message or to get some basic inputs (such as Yes/No or OK/Cancel).
When the MsgBox is displayed, the execution of code is halted. It waits for the user to click a button and then an action is performed based on the button clicked by the user.
VBA MsgBox Syntax
Prompt – This is a required parameter. It displays the message that you see in the MsgBox.
Buttons – This is optional parameter. It determines which type of button and icon are to be displayed in the MsgBox. For example, if you use vbOkOnly, it will show only the OK button. If you use vbOKCancel, it will show both the OK and Cancel buttons. If it is left blank, the default value for buttons is vbOkOnly.
Title − This is optional parameter. Here you can specify caption you want to display in the title bar of the message box. If the title is not specified, it will show the name of the application.
Helpfile − This is optional parameter. You can specify a help file that can be accessed when a user clicks on the Help button.
Context − This is optional parameter. A numeric expression that identifies the Help context number assigned to the appropriate Help topic. If context is provided, helpfile must also be provided.
Excel VBA MsgBox Button Constants
You can provide any of the following values as Button parameter to specify what buttons you want in message box.
vbOKOnly – Shows OK button only.
vbOKCancel – Shows OK and Cancel buttons.
vbAbortRetryIgnore – Shows Abort, Retry, and Ignore buttons.
vbYesNoCancel – Shows Yes, No, and Cancel buttons.
vbYesNo – Shows Yes and No buttons.
vbRetryCancel – Shows Retry and Cancel buttons.
Below are the examples of different type of MsgBox buttons.
MsgBox Buttons – vbOKOnly
Default message box contains only OK button. If you use only the prompt and do not specify any other argument, it will display only OK button in message box. Below given is the sample code for that.
Sub MsgBoxExample() MsgBox "Sample message for you." End Sub
Below given code uses vbOKOnly button constant. The code gives the same output.
Sub MsgBoxExample() MsgBox "Sample message for you.", vbOKOnly End Sub
MsgBox Buttons – OK & Cancel
If you want to show the OK and the Cancel button, you need to use the vbOKCancel constant.
MsgBox "Sample message for you.", vbOKCancel
MsgBox Buttons – Abort, Retry, and Ignore
If you want to show the Abort, Retry, and the Ignore buttons, you can use the vbAbortRetryIgnore contant.
MsgBox " What do you want to do?", vbAbortRetryIgnore
MsgBox Buttons – Yes and No
If you want to show Yes and No buttons, you need to use the vbYesNo constant.
MsgBox "Do you want to continue?", vbYesNo
MsgBox Buttons – Yes, No and Cancel
If you want to show Yes, No and Cancel buttons, you need to use the vbYesNoCancel constant.
MsgBox "Do you want to continue?", vbYesNoCancel
Excel VBA MsgBox Icon Constants
You can use Excel VBA MsgBox icon constants to customize the icons that are displayed in the MsgBox. You can provide any of the following values along with button constants, by putting a + sign in between the codes, to show icons in message box.
vbCritical – Displays Critical Message icon.
vbQuestion – Displays Warning Query icon.
vbExclamation – Displays Warning Message icon.
vbInformation – Displays Information Message icon.
Below are sample code examples of using VBA MsgBox icon constants.
Below code displays Critical Message icon with Yes, No buttons.
MsgBox "Sample message for you.", vbYesNo + vbCritical
Below code displays Warning Query icon with Yes, No buttons.
MsgBox "Sample message for you.", vbYesNo + vbQuestion
Below code displays Warning Message icon with Yes, No buttons.
MsgBox "Sample message for you.", vbYesNo + vbExclamation
Below code displays Information Message icon with Yes, No buttons.
MsgBox "Sample message for you.", vbYesNo + vbInformation
Using Values Returning from MsgBox
The MsgBox function returns any one of the following values that you can use further to identify which button the user clicked in the message box.
vbOK – OK was clicked
vbCancel – Cancel was clicked
vbAbort – Abort was clicked
vbRetry – Retry was clicked
vbIgnore – Ignore was clicked
vbYes – Yes was clicked
vbNo – No was clicked