Talking to the User: Message Box and Input Box
1. MsgBox
MsgBox (Message Box) brings up a dialog box with a title bar, a message of our choice, and a choice of icons and buttons. The function can return a value that tells us which button the user clicked.
We have seen MsgBox in passing in earlier posts. Here is the syntax of the MsgBox statement:MsgBox prompt [, buttons] [, title]
and, of the MsgBox Function:ReturnValue = MsgBox( prompt [, buttons] [, title] )
The buttons argument settings are:
| Constant | Value | Description |
| vbOKOnly | 0 | Display OK button only (default). |
| 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 Question icon. |
| vbExclamation | 48 | Display Warning Message icon. |
| vbInformation | 64 | Display Information Message icon. |
| vbDefaultButton1 | 0 | Make First button the default (it already is) |
| vbDefaultButton2 | 256 | Make Second button the default. |
| vbDefaultButton3 | 512 | Make Third button the default. |
| vbApplicationModal | 0 | Application modal; the user must respond to the message box before continuing work in the current application (default). |
| vbSystemModal | 4096 | System modal; all applications are suspended until the user responds to the message box. |
| vbMsgBoxHelpButton | 16384 | Adds Help button to the message box |
| VbMsgBoxSetForeground | 65536 | Specifies the message box window as the foreground window |
| vbMsgBoxRight | 524288 | Text is right-aligned |
You can add one constant from each group to form the buttons parameter. E.g. vbYesNoCancel + vbQuestion + vbDefaultButton3
For the MsgBox Function, you can tell which button the user pressed. Compare the ReturnValue against these built-in constants:
| Constant | Value | Button chosen |
| vbOK | 1 | OK |
| vbCancel | 2 | Cancel |
| vbAbort | 3 | Abort |
| vbRetry | 4 | Retry |
| vbIgnore | 5 | Ignore |
| vbYes | 6 | Yes |
| vbNo | 7 | No |
For example, using vbNo:If MsgBox("Do you want to proceed?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
Here, the MsgBox displays the Question icon (vbQuestion) and the Yes and No buttons (vbYesNo). If the user clicks No (ReturnValue equals vbNo), the Sub ends.
2. InputBox$
This is a bit different to MsgBox in that it allows you to ask the user to input a string. There are always OK and Cancel buttons. Pressing Cancel returns a blank string (“”). So does clicking OK without typing anything. It is advisable to check for this before further processing.
The syntax is:ReturnSt$ = InputBox$(prompt[, title][, default][, xpos][, ypos]])
You can omit any argument except the prompt. But if you do use one, you must use all the preceding commas too. It is particularly useful to provide a default value.
In both MsgBox and InputBox, you can force new lines in your message by adding in the constant vbLf, for example:Nam$ = InputBox(“What is your name?” & vbLf & vbLf & _
“(Click Cancel if you don't know)”, “Name Entry Box”, PrevNam$)
3. Example
You can get the file Macros.xlsm free of charge from us. If you have, Click the button Message Demonstration on Sheet1. Repeat it a few times, taking different choices. Do not forget to see how it behaves if you enter something that is not a number.
Then press Alt+F11 for the VB Editor and look in the Module Code_Examples for the Sub Messages.
Otherwise, copy and paste the code below into a blank worksheet and then run it.
Sub Messages() ' Message Demonstration:
Dim St$
If vbNo = MsgBox("Would you like to proceed?", _
vbQuestion + vbYesNo, "The Inquisition!") Then Exit Sub
St$ = InputBox("Gimmie a Number, kid!", "Any number will do!")
If St$ = "" Then
MsgBox "Hey! You hit cancel!", vbExclamation, "Coward!"
ElseIf IsNumeric(St$) Then
MsgBox "Thank you for: " & St$, , "Number OK"
Else
MsgBox "Since when is '" & St$ & "' a Number?", vbCritical
End If
End Sub
This procedure first uses a MsgBox function to return a vbYes or vbNo value (set using vbYesNo). If it is a vbNo, the program ends with an Exit Sub. Otherwise, an InputBox asks for a number, which goes into the string St$. That string is checked against blank (“”). If it is blank, the program displays a MsgBox with the vbExclamation icon, and drops through to End Sub.
Otherwise, the program uses the IsNumeric() function to check if the string is numeric. In either case, it then responds with an appropriate message.