Excel VBA Msgbox and handling of return value

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 using1) carriage return – Chr(13) or vbCr OR 2) line feed – Chr(10) or vbLf OR3) carriage return + line feed – Chr(13)& Chr(10) or vbCrLf  OR4) 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 Return Value Value vbOK 1 vbCancel 2 vbAbort 3 vbRetry 4 vbIgnore 5 vbYes 6 vbNo 7
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

Excel VBA Msgbox and handling of return value