The Excel message box is a handy tool for displaying information to the user and getting a response from them, as well as for debugging purposes. MsgBox is a function, and your code is halted until the message box is dismissed by the user. When a message box is displayed, you can press Ctrl+C to copy the contents of the message box to the Windows clipboard.
When you press Ctrl+C to copy the contents of the above message box to the Windows clipboard and then paste that into a text file you will get the following.
--------------------------- Microsoft Excel --------------------------- 34.2 --------------------------- OK ---------------------------
Statements and Expressions
The message box is a good tool for returning the results of an expression. For example, consider the following expression: Range(“A1”).Value. This just returns the value of the cell A1. It is not a statement because it is not assigning a value to the cell A1.
Sub StatementsExpressions()
Range("A1").Value = 34.2
MsgBox Range("A1").Value
End Sub
The syntax for MsgBox is:
MsgBox(prompt[,buttons][,title][,helpfile, context])
- prompt: Required. The text displayed in the message box.
- buttons: Optional. A numeric expression that determines which buttons and icon are displayed in the message box.
- title: Optional. The caption in the message box window.
- helpfile, context: Optional. The helpfile and Help topic.
| Constant | Value | Description |
| vbOKOnly | 0 | Display only an OK button. |
| vbOKCancel | 1 | Display OK and Cancel buttons. |
| vbAbortRetryIgnore | 2 | Display Abort, Retry, and Ignore buttons. |
| vbYesNoCancel | 3 | Display Yes, No, and Cancel buttons. |
| vbYesNo | 4 | Display Yes and No buttons. |
| vbRetryCancel | 5 | Display Retry and Cancel buttons. |
| vbCritical | 16 | Display Critical Message icon. |
| vbQuestion | 32 | Display Warning Query icon. |
| vbExclamation | 48 | Display Warning Message icon. |
| vbInformation | 64 | Display Information 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. |
| vbSystemModal | 4096 | All applications are suspended until the user responds to the message box (might not work under all conditions). |
| vbMsgBoxHelpButton | 16384 | Display a Help button. To display help when this button is clicked, use the helpfile and context argu- ments. |
You can ask a question and get a response from the user. Below is an example of the YesNo. Take a look at the other two MsgBox lines. Note that the single argument is not enclosed in parentheses because the MsgBox result is not assigned to a variable.
Sub GetResponseFromMsgBox()
Dim Ans As Long
Ans = MsgBox("Continue?", vbYesNo)
Select Case Ans
Case vbYes
MsgBox "You said yes"
Case vbNo
MsgBox "You said no"
End Select
End Sub
If we wanted a Yes No message box that also displayed a Help button, we would use the following line and use the number 16388, which is 4 plus 16384. We could also just add up the constants. The next example shows that.
Ans = MsgBox("Continue?", 16388)
Dim Config As Long
Dim Ans As Long
Config = vbYesNo + vbQuestion + vbDefaultButton2
Ans = MsgBox("An error occurred. Continue?", Config)
The MsgBox return value constants.
| Constant | Value | Button Clicked |
| vbOK | 1 | OK |
| vbCancel | 2 | Cancel |
| vbAbort | 3 | Abort |
| vbRetry | 4 | Retry |
| vbIgnore | 5 | Ignore |
| vbYes | 6 | Yes |
| vbNo | 7 | No |