MS-Excel / General Formatting

---

Storing User Input in a Variable

Your VBA programs will usually be self-contained and run just fine on their own. However, you'll likely come across situations where you'll require some kind of custom input. For example, you might have a procedure that adjusts various aspects of a Word document. You could insert the name and location of a Word document into the procedure (this is called hard-coding the data), but that's not very flexible if your procedure is capable of working with different documents. A better idea is to have your procedure prompt for the name and location of a document. Your procedure could then take that data and use it to work on the specified document.

Whatever type of input you ask for, the result needs to be stored in a variable so that the rest of your procedure can access it. The next couple of sections take you through some VBA techniques that enable you to prompt for data and then store that data in a variable.

Getting Input Using MsgBox

This is a very useful function, so let's take a closer look at it. Here is the full syntax of this function:

MsgBox(Prompt[, Buttons][, Title][, HelpFile][, Context])

Prompt
The message you want to display in the dialog box. (You can enter a string up to 1,024 characters long.)

Buttons
A number or constant that specifies, among other things, the command buttons that appear in the dialog box. (See the next section.) The default value is 0.

Title
The text that appears in the dialog box title bar. If you omit the title, VBA uses the name of the current program (for example, Microsoft Excel).

HelpFile
The text that specifies the Help file that contains the custom help topic. If you enter HelpFile, you also have to include Context. If you include HelpFile, a Help button appears in the dialog box.

Context
A number that identifies the help topic in HelpFile.

There are a number of tutorials online that show you how to create a Help file.Type "creating help files" into your favorite search engine.

Note: The MsgBox function, like all VBA functions, needs parentheses around its arguments only when you use the function's return value.

Tip: For long prompts,VBA wraps the text inside the dialog box. If you'd prefer to create your own line breaks, use VBA's vbCrLf constant to insert a carriage-return and line-feed between each line:

MsgBox "First line" & vbCrLf & "Second line"

Setting the Style of the Message

The default message dialog box displays only an OK button. You can include other buttons and icons in the dialog box by using different values for the Buttons parameter. Table below lists the available options.

The MsgBox Buttons Parameter Options

Constant	Value	Description
Buttons
vbOKOnly	0	Displays only an OK button. (This is the default.)
vbOKCancel	1	Displays the OK and Cancel buttons.
vbAbortRetryIgnore	2	Displays the Abort, Retry, and Ignore buttons.
vbYesNoCancel	3	Displays the Yes, No, and Cancel buttons.
vbYesNo	4	Displays the Yes and No buttons.
vbRetryCancel	5	Displays the Retry and Cancel buttons.
Icons
vbCritical	16	Displays the Critical Message icon.
vbQuestion	32	Displays the Warning Query icon.
vbExclamation	48	Displays the Warning Query icon.
vbInformation	64	Displays the Information Message icon.
Default Button
vbDefaultButton1	0	The first button is the default (that is, the button selected when the user presses Enter).
vbDefaultButton2	256	The second button is the default.
vbDefaultButton3	512	The third button is the default.
Modality
vbApplicationModal	0	The user must respond to the message box before continuing work in the current application.
vbSystemModal	4096	All applications are suspended until the user responds to the message box.

You derive the Buttons argument in one of two ways:

• By adding up the values for each option. For example, if you want the OK and Cancel buttons (value 1) and the Warning Message icon (value 48), then you specify the value 49.
• By using the VBA constants separated by plus signs (+). This is the better way to go because it makes your code much easier to read.

For example, Listing below shows a procedure named ButtonTest Here, three variables-msgPrompt, msgButtons, and msgTitle-store the values for the MsgBox function's Prompt, Buttons, and Title arguments, respectively. In particular, the following statement derives the Buttons argument:

msgButtons = vbYesNo + vbQuestion + vbDefaultButton2

You also could derive the Buttons argument by adding up the values that these constants represent (4, 32, and 256, respectively), but the procedure becomes less readable that way.

Listing - A Procedure That Creates a Message Dialog Box

Sub ButtonTest()
    Dim msgPrompt As String, msgTitle As String
    Dim msgButtons As Integer, msgResult As Integer

    msgPrompt = "Are you sure you want to display " & vbCrLf & _
                "the worksheet names?"
    msgButtons = vbYesNo + vbQuestion + vbDefaultButton2
    msgTitle = "Display Worksheet Names"

    msgResult = MsgBox(msgPrompt, msgButtons, msgTitle)
End Sub