MS-Excel / General Formatting

---

Getting Input Using InputBox

As you've seen, the MsgBox function lets your procedures interact with the user and get some feedback. Unfortunately, this method limits you to simple command-button responses. For more varied user input, you need to use a more sophisticated technique. The rest of this tutorial shows you just such a method: prompting the user for input using the InputBox function.

The InputBox function displays a dialog box with a message that prompts the user to enter data, and it provides a text box for the data itself. Here's the syntax for this function:

InputBox(Prompt[, Title][, Default][, Xpos][, Ypos][, HelpFile][, Context])

Prompt
The message you want to display in the dialog box (1,024-character maximum).

Title
The text that appears in the dialog box title bar. The default value is the null string (nothing).

Default
The default value displayed in the text box. If you omit Default, the text box is displayed empty.

Xpos
The horizontal position of the dialog box from the left edge of the screen. The value is measured in points (there are 72 points in an inch). If you omit Xpos, the dialog box is centered horizontally.

Ypos
The vertical position, in points, from the top of the screen. If you omit Ypos, the dialog is centered vertically in the current window.

HelpFile
The text specifying 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.

For example, Listing below shows a procedure called InputBoxText that uses the InputBox method to prompt the user for data. The dialog box that appears. The result is stored in the inputData variable. If the user didn't enter data, the function returns nothing, which is represented in VBA by the string value "" (this is called the null string). The procedure uses the If statement to check whether the value stored in inputData is "" and, if it's not, it runs MsgBox to display the entered data.

Listing - A Procedure That Prompts the User for Input and Then Displays the Data

Sub InputBoxTest()
    Dim inputData As String
    '
    ' Get the data
    '
    inputData = InputBox("Enter some text:", "Input Box Text")
    '
    ' Check to see if any data was entered
    '
    If inputData <> "" Then
        '
        ' If so, display it
        '
        MsgBox inputData
    End If
End Sub

Caution: The InputBox function returns a string (the null string) if you click Cancel.What do you do, however, if you want to use InputBox to get, say, a numeric value? This means that the result stored in your variable could be either a string or a number.The solution here is to declare your variable with the Variant type.That way,VBA will store the result-whatever it is-without generating an error.