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 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 + vbExclamationmsgbox_02

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.msgbox_07

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.msgbox_01

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

msgbox_03

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

msgbox_04

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

msgbox_05

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

msgbox_06

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

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *