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:
Excel VBA Inputbox and Application.Inputbox
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])
| Prompt | Required. String expression displayed as the message in the dialog box. The maximum length of Prompt is approximately 1024 characters, depending on the width of the characters used. If Prompt consists of more than one line, you can separate the lines using
1) carriage return – Chr(13) or vbCr OR 2) line feed – Chr(10) or vbLf OR 3) carriage return + line feed – Chr(13)& Chr(10) or vbCrLf OR 4) vbNewLine, suitable for different platforms |
||||||||||||||||
| Buttons | Optional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. You can add more than one Buttons using + sign, such as vbOKCancel + vbExclamation
If you omit Buttons, the default value is zero (The user must respond to the message box before continuing work in the current application.)If you choose a Button, you need to define a variable to return a value, otherwise you will receive the following error message. The return values are as follows
|
||||||||||||||||
| Title | Optional. String expression displayed in the title bar of the dialog box.
If you omit Title, the application name (Microsoft Excel) is placed in the title bar. |
||||||||||||||||
| helpfile | Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided. | ||||||||||||||||
| context | Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided. |
| Buttons Argument (blue is about Modality) |
||
| Member | Value | Description |
| 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
‘delete something
ElseIf sinput = vbCancel Then
‘no action
End If
End Sub
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.
Outbound References
https://msdn.microsoft.com/en-us/library/office/gg251821%28v=office.15%29.aspx