The VBA MsgBox Function
Related Function:
VBA InputBox
Description
The VBA MsgBox function displays a modal message box. The function returns a VbMsgBoxResult enumeration, which tells you which button has been selected by the user.
The syntax of the function is:
MsgBox( Prompt, [Buttons],
[Title], [HelpFile],
[Context] )
Where the function arguments are:
| Prompt | - | The text string that you want to appear in the message box. | |||||||||||||||||||||
| [Buttons] | - |
An optional argument that specifies the properties of the message box. The main options, defining the number and type of buttons to be displayed, are:
If the [Buttons] argument is omitted, or if none of the above values are specified, the default value vbOKOnly is used. There are also a number of other [Buttons] values that specify additional properties for the message box. These values can be used in conjunction with other options. + Show additional options |
|||||||||||||||||||||
| [Title] | - | A optional text string that specifies a title to be displayed at the top of the message box. | |||||||||||||||||||||
| [HelpFile] | - |
An optional string argument, identifying the Help file that is to be displayed if the 'Help' button is selected. If the [HelpFile] argument is provided, the [Context] argument must also be provided. |
|||||||||||||||||||||
| [Context] | - |
An optional numeric value that is the context ID for the Help topic to be displayed. If the [Context] argument is provided, the [HelpFile] argument must also be provided. |
The VBA MsgBox function returns one of the following VbMsgBoxResult enumeration values, informing the developer of the option that has been selected by the user:
| VbMsgBoxResult | Value | Button Selected |
|---|---|---|
| vbOK | 1 | OK |
| vbCancel | 2 | Cancel |
| vbAbort | 3 | Abort |
| vbRetry | 4 | Retry |
| vbIgnore | 5 | Ignore |
| vbYes | 6 | Yes |
| vbNo | 7 | No |
VBA MsgBox Function Examples
Example 1 - Simple Welcome Message
|
' Display a Welcome message.
MsgBox( "Welcome!" )
|
The above code displays the following message box to the user:
The VBA code pauses while the message box is displayed. When the user clicks on the OK button, the program will continue to run.
Example 2 - MsgBox Returning Yes or No
|
' Ask the user if they want to continue a long calculation.
Dim answer As VbMsgBoxResult
If answer = vbYes Then
answer = MsgBox( "This calculation could take several minutes. Do you want to continue?", vbYesNo )
' Code to continue the calculation
Else
' Code to stop the calculation
End If
|
The above code displays the following message box to the user:
Note that the above VBA code stores the result from the MsgBox function in a variable called answer. The value of this variable is then used in an If statement.
Example 3 - MsgBox Function Receiving Two [Button] Argument Values
|
' Failed file read. Ask the user if they want to retry or cancel.
Dim answer As VbMsgBoxResult
If answer = vbRetry Then
answer = MsgBox( "Failed to find input file, data.txt", vbRetryCancel + vbExclamation )
' Code to retry the file read
Else
' Code to cancel the file read
End If
|
The above code displays the following message box to the user:
Note that, in the above VBA code:
- Two values are supplied for the [Buttons] argument of the MsgBox function. These are separated by the + symbol (i.e. vbRetryCancel + vbExclamation).
- The vbExclamation argument has caused the Exclamation icon to appear in the message box.
- The result from the MsgBox function is stored in a variable called answer. The value of this variable is then used in an If statement.
Example 4 - Forcing a Line Break in a Message Box
You can use the constant vbNewLine to represent a line break in a VBA string. This must be concatenated with the other components of a String, using the & operator.
An example of this is shown below:
|
' Display the value in cell A1 of the active worksheet.
MsgBox( "Current Value in Cell A1:" & vbNewLine & Range( "A1" ) )
|
The above code displays the following message box to the user:
Note that, in the above VBA code, the & operator has been used to concatenate the string "Current Value in Cell A1:" with vbNewLine, and the value from cell A1 of the active sheet. The resulting string is then supplied as the Prompt argument to the MsgBox function.