This Excel tutorial explains how to use Excel VBA Msgbox with carriage return such as vbCr, vbNewLine, and handling of return value such as vbOK, vbCancel, vbRetry
You may also want to read:
What is Excel VBA MsgBox?
Excel VBA MsgBox is a prompt box that is used to prompt user for important information, usually provide user with OK and Cancel buttons. Note that there is another prompt box called InputBox which receives text input from user but it is not in the scope of this article.
Why do you need Excel VBA MsgBox?
There are several reasons that you may need Excel VBA Msgbox:
1) When you run a Sub or a Function, you will see the final result only and the result may not be what you expect while there is no error message. In that case, you can insert a Msgbox code within a Procedure to check the value of a variable at specific line of code.
2) To warn user or confirm an action, such as asking users if they confirm to delete data
Syntax of Excel VBA MsgBox
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
|Buttons Argument (blue is about Modality)
|vbAbortRetryIgnore||2||Display Abort, Retry, and Ignore buttons.|
|vbApplicationModal||0||The user must respond to the message box before continuing work in the current application.|
|vbCritical||16||Display Critical Message icon.|
|vbDefaultButton1||0||First button is default.|
|vbDefaultButton2||256||Second button is default.|
|vbDefaultButton3||512||Third button is default.|
|vbDefaultButton4||768||Fourth button is default.|
|vbExclamation||48||Display Warning Message icon.|
|vbInformation||64||Display Information Message icon.|
|vbMsgBoxHelpButton||16384||Adds a Help button to the message box|
|vbMsgBoxRight||524288||Text is right aligned|
|vbMsgBoxRtlReading||1048576||Specifies text should appear as right-to-left reading on Hebrew and Arabic systems|
|vbMsgBoxSetForeground||65536||Specifies the message box window as the foreground window|
|vbOKCancel||1||Display OK and Cancel buttons.|
|vbOKOnly||0||Display OK button only (this is the default).|
|vbQuestion||32||Display Warning Query icon.|
|vbRetryCancel||5||Display Retry and Cancel buttons.|
|vbSystemModal||4096||System modal; all applications are suspended until the user responds to the message box|
|vbYesNo||4||Display Yes and No buttons.|
|vbYesNoCancel||3||Display Yes, No, and Cancel buttons.|
Example of Excel VBA MsgBox
Step 1 – MsgBox with two lines of message
Public Sub test1() sInput = MsgBox("Are you sure to delete the data?" & vbNewLine & "Data will be deleted permanently") End Sub
Step 2 – Add OK / Cancel Button
Public Sub test2() sInput = MsgBox("Are you sure to delete the data?" & vbNewLine & "Data will be deleted permanently", vbOKCancel) End Sub
Step 3 – Add exclamation logo
Public Sub test3() sInput = MsgBox("Are you sure to delete the data?" & vbNewLine & "Data will be deleted permanently", vbOKCancel + vbExclamation) End Sub
Step 4 – Add message box title
Public Sub test4() sInput = MsgBox("Are you sure to delete the data?" & vbNewLine & "Data will be deleted permanently", vbOKCancel + vbExclamation, "Warning") End Sub
Step 5 – Condition on return value
Public Sub test5()
sinput = MsgBox(“Are you sure to delete the data?” & vbNewLine & “Data will be deleted permanently”, vbOKCancel + vbExclamation, “Warning”)
If sinput = vbOK Then
ElseIf sinput = vbCancel Then
In the above example, we have OK and Cancel Buttons in MsgBox. If you click on the red cross on the top right, the return value is same as clicking on Cancel Button.
If your MsgBox contains only 1 button, clicking on the red cross is same as clicking on the only button, therefore red cross doesn’t necessarily mean Cancel.