User Input and Output
The following sections describe various methods of displaying information and obtaining input from the user.
MsgBox
The MsgBox command allows you to display a dialog box containing an informative message. MsgBox temporarily halts the macro until the user closes the message dialog box.
The MsgBox command has the following form:
MsgBox message [,buttons [,title] ]
message is the text to display in the message, buttons is a constant that indicates the type of buttons to display, and title is the title displayed in the dialog box title bar.
To display a simple message, use a command such as this one:
MsgBox "It's Saturday night!"
This command displays the dialog box.
Here's a MsgBox call that specifies the type of buttons and a title:
MsgBox "It's Saturday night!", vbOKCancel, "Live from London!"
The buttons argument controls three items at a time:
- Buttons that appear in the dialog box
- The icon that appears in the dialog box
- The button that is the default
(If you omit the default button argument, the first button is assumed to be the default.) Table-2 lists the values for these settings.
Table-2 MsgBox Type ValuesGroup | Constant | Meaning |
---|---|---|
Buttons | vbOKOnly | OK only |
vbOKCancel | OK and Cancel | |
vbAbortRetry Ignore | Abort, Retry, and Ignore | |
vbYesNoCancel | Yes, No, and Cancel | |
vbYesNo | Yes and No | |
vbRetryCancel | Retry and Cancel | |
Icon | vbCritical | Critical icon |
vbQuestion | A question mark | |
vbExclamation | An exclamation mark | |
vbInformation | Information only | |
Default button | vbDefault Button1 | First button (OK, Yes, or Abort) |
vbDefault Button2 | Second button (Cancel, No, or Retry) | |
vbDefault Button3 | vbDefault Button3 |
You can put these constants together to create composite styles. For example, this MsgBox command displays OK and Cancel buttons and a Stop symbol and makes the Cancel button the default:
MsgBox "It's Saturday night!", vbOKCancel + vbExclamation + vbDefaultButton2, "Live from London!"
The resulting dialog box shown.
MsgBox returns a value that indicates which button was clicked, as described in Table-3.
Table-3 MsgBox Return ValuesConstant Numeric Value Button That Is Clicked vbOK 1 OK button vbCancel 2 Cancel vbAbort 3 Abort vbRetry 4 Retry vbIgnore 5 Ignore vbYes 6 Yes vbNo 7 No
InputBox
The VBA function InputBox displays a dialog box that includes a single text field in which the user can type a response. The user's input is then returned to the macro as the function's return value.
The InputBox function accepts three arguments:
InputBox(prompt [,title] [,default])
For example, the following InputBox function asks the user to enter a name:
Name=InputBox("Type a name:")
This example shows how to provide your own title for the input dialog box and display a default choice:
Name=InputBox("Type a name:", "The Name Game", UserName)
The user's response returns in the Name variable. The dialog box that's displayed.
In this tutorial:
- Programming with VBA
- Basic Structure of VBA Macros
- Understanding the Basic Elements of VBA
- Working with Variables and Data
- Using static variables
- Using Strings
- Of Objects, Properties, and Methods
- Getting to know the object model
- Using the With statement
- Controlling Your Programs
- The ElseIf structure
- For/Next Loops
- User Input and Output
- User-Defined Procedures and Functions