Writing to the Screen and Accepting Input

---

Accepting input from users and providing status messages are common tasks that you'll use within many procedures. VBA lets you use two common Windows methods of displaying and asking for information from a user, which makes your macros easier to use because they present a familiar interface to the user.

Creating a Message Box

When small informational messages or simple questions need to be asked, the MsgBox (message box) function can be used. Message boxes are useful for a number of reasons. Besides being familiar aspects of just about any Windows application, they are extremely simple and easy to use. Here is a list of some common tasks for which you can use message boxes.

• Inform the user of an action that is about to take place, and possibly allow them the option to cancel the action or choose a different action.

• Inform the user of an error condition that has occurred, and allow them to take corrective actions.

• Inform the user that a particular task has been completed successfully or not. The message box can inform the user exactly what was done to perform the task.

A primary advantage of using a message box is that it lets you present a message to the user that can't be ignored. It's even possible to force the user to react to the message box by not allowing them to switch to or open any other application.

As useful as message boxes are, they do have several limitations, such as the following:

• A message box can present only one, two, or three buttons for users to choose from, which means a limited number of options for the user.

• The buttons are available only in predefined sets-you can't place a custom button into a message box.

• No other features are available in a message box. You can't add additional controls to present more information/options to a user.

Despite these limitations, the message box is a very handy tool for any programmer. The syntax of a message box statement is shown here.

MsgBox(prompt[, buttons] [, title][, helpfile, context]) as Integer

• prompt A required argument for the MsgBox function that contains the text that is displayed within the message box. The text can be no longer than about 1024 characters, depending upon the width of the characters. If the text contains multiple lines, you can specify the line breaks by including a carriage return with the constant vbCr, (Chr$(13)), linefeed with the vbLf constant, (Chr$(10)), or carriage return/linefeed combination with the vbCrLf constant.

  Note 

  While it's easier to use the intrinsic constants for a carriage return and a line feed, you might see (Chr$(13)) for a carriage return, (Chr$(10)) for a line feed, and (Chr$(13) & Chr$(10)) for both.

• buttons Optional numerical argument used to specify the buttons and the icon that is to be displayed within the message box. By adding the value of the selected options together, you can specify not only which buttons and icon to display, but also which button is the default button and the modality of the message box. You can use the Visual Basic intrinsic constants to specify options. See Table 4-9 for a complete list of options available. The default button is selected if the user presses the Enter key. If buttons is not given, the default value of 0 is used.

• title Optional string argument used to specify the text to be displayed within the message boxes title bar. If omitted, 'Microsoft Office Excel' is displayed.

• helpfile Optional string argument used to specify the help file used to provide context-sensitive help. If helpfile is specified, context must also be specified.

• context Optional numerical argument that is the Help context number assigned to the appropriate Help topic. If context is provided, helpfile must also be provided.

The MsgBox function returns an integer value that indicates which button was clicked by the user. These values are listed in Table 4-10.

Table 4-9: Button Options for Message Boxes

Intrinsic Constant	Value	Description
Buttons
vbOkOnly	0	Displays an OK button only.
vbOkCancel	1	Displays an OK and a Cancel button.
vbAbortRetryIgnore	2	Displays three buttons labeled Abort, Retry, and Ignore.
vbYesNoCancel	3	Displays three buttons labeled Yes, No, and Cancel.
vbYesNo	4	Displays buttons labeled Yes and No.
vbRetryCancel	5	Displays buttons labeled Retry and Cancel.
Icons
vbCritical	16	Displays a solid red circle enclosing a white X.
vbQuestion	32	Displays a cartoon balloon enclosing a question mark.
vbExclamation	48	Displays a yellow triangle enclosing an exclamation point.
vbInformation	64	Displays a cartoon balloon enclosing a lowercase letter i.
Default Button
vbDefaultButton1	0	Sets the first button as the default button.
vbDefaultButton2	256	Sets the second button as the default button.
vbDefaultButton3	512	Sets the third button as the default button.
vbDefaultButton4	768	Sets the fourth button (such as a Help button) as the default button.
Modality
vbApplicationModal	0	Marks the message box as application modal. Stops all processing of the current application until the message box is dismissed. Does not interfere with any other applications.
vbSystemModal	4096	Marks the message box as system modal; it will always appear as the topmost window regardless of which application the user switches to.
vbMsgBoxHelpButton	16384	Adds a Help button to the message box.
vbMsgBoxSetForeground	65536	Causes the message box to be displayed in the foreground.
vbMsgBoxRight	524288	Causes the text in the message box to be right- aligned.
vbMsgBoxRtlReading	1048576	Causes the text to be displayed right-to-left on Hebrew and Arabic systems.

Table 4-10: Values Returned by a Message Box

Intrinsic Constant	Value
vbOk	1
vbCancel	2
vbAbort	3
vbRetry	4
vbIgnore	5
vbYes	6
vbNo	7

If a Cancel button is displayed in the message box, pressing Esc has the same effect as clicking it. You should select exactly one option from each group; if more than one is selected, Excel will use the option with the highest value.

You can display a simple message box just by providing the title argument.

Sub Welcome()
    MsgBox "Welcome to Excel."
End Sub

The preceding procedure produces a simple message box with only the text and an OK button displayed. Because the statement doesn't include a value for the message box title, the title defaults to the name of the application (as you can see in Figure 4-13).

Figure 4-13: It's easy to create message boxes with brief welcome or informational messages.

Besides using literal strings to display messages, you can also use string variables. The following code fragment displays a message box with two lines of text in the message, an information icon, and a title in the title bar, with the result shown in Figure 4-14.

Figure 4-14: It's not that much harder to create a simple message box with two lines of text.

Sub Travel()
    strPrompt = "Welcome!" & vbCrLf & "Enter your travel data below."
    MsgBox strPrompt, vbInformation, "Travel Voucher Records"
End Sub

When choosing an icon to display in the message box, you can use the following guidelines, provided by Microsoft in the Windows User Interface guidelines:

• The information icon should be used to provide results to the user of a command previously issued. No choices should be offered, and only the OK button should ever be displayed with the information icon.

• The exclamation icon should be used to warn the user of a problem or situation that requires a decision from the user before continuing. This is especially true of situations where data might be irreversibly changed or erased.

• The critical icon should be used to inform the user of a critical error or problem that needs to be corrected before further processing can be done.

• The question icon should not be used in any instance and is provided only for backward compatibility.

Creating an Input Box

An input box is similar to a message box in that it displays text, but it has the additional functionality of being able to accept text responses from users. Instead of presenting the user with a few buttons that can be clicked in response to messages displayed in the window, input boxes contain a text box where the user can type in responses. The input box displays a prompt and title just as a message box does, but no icons are displayed and an OK and a Cancel button are always present. An input box that asks for a user's name would look similar to Figure 4-15.

Figure 4-15: Creating an input box would allow users to enter personal information, such as their name.

The syntax for an input box is shown here.

InputBox (prompt [, title][, default][, xpos][, ypos][, helpfile, context]) as String

• prompt Required argument for the InputBox function that contains the text that is displayed within the input box. The text can be no longer than about 1024 characters, depending upon the width of the characters. If the text contains multiple lines, you can specify the line breaks by including a carriage return with vbCr, a linefeed with vbLf, or a carriage return/linefeed combination with vbCrLf. You can also use the Visual Basic intrinsic constants vbCr, vbLf, and vbCrLf to represent the characters.

• title Optional string argument used to specify the text to be displayed within the input boxes title bar. If omitted, 'Microsoft Office Excel' is displayed.

• default Optional string value that is displayed within the text box as the default value if no other information is entered. The user can erase or modify the default answer.

• xpos ptional numeric expression that represents the number of twips from the left edge of the screen to the left edge of the input box. If xpos is omitted, the input box is centered horizontally.

  Note 

  A twip (twentieth of a point) is 1/1440th of an inch or 1/567th of a centimeter. So, to start an input box half an inch in from the left edge of the screen, you would assign an xpos value of 720.

• ypos Optional numeric expression that represents the number of twips from the top edge of the screen to the top edge of the input box. If ypos is omitted, the input box is centered vertically.

• helpfile Optional string argument used to specify the help file used to provide context-sensitive help. If helpfile is specified, context must also be specified.

• context Optional numerical argument that is the Help context number assigned to the appropriate Help topic. If context is provided, helpfile must also be provided.

The InputBox function returns a string value, so if the text box is blank or the Cancel button pressed, the returned string is empty.

The returned value from an input box is usually stored within a string variable so that it can be processed further. The following code fragment displays a message box asking for the user's place of birth. All parameters are entered as variables, although literals or constants could have also been used.

Sub BirthCity()
Dim strResponse as String, strPrompt as String, strTitle as String
strResponse = "New Orleans"
strPrompt = "Please enter your city of birth."
strTitle = "My InputBox"
strResponse = InputBox(strPrompt, strTitle, strResponse)
End Sub

---