Using the Input Box Function

The InputBox function displays a dialog box with a message that prompts the user to enter data. This dialog box has two buttons—OK and Cancel. When you click OK, the InputBox function returns the information entered in the text box. When you select Cancel, the function returns the empty string (" "). The syntax of the InputBox functon is as follows:

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

the Chr(13) or Chr(10) functions (see examples of using the MsgBox function earlier in this chapter). All of the remaining InputBox arguments are optional.

The second argument, title, allows you to change the default title of the dialog box. The default value is "Microsoft Excel."

The third argument of the InputBox function, default, allows the display of a default value in the text box. If you omit this argument, the empty edit box is displayed.

The following two arguments, xpos and ypos, let you specify the exact position where the dialog box should appear on the screen. If you omit these arguments, the input box appears in the middle of the current window. The xpos argument determines the horizontal position of the dialog box from the left edge of the screen. When omitted, the dialog box is centered horizontally. The ypos argument determines the vertical position from the top of the screen. If you omit this argument, the dialog box is positioned vertically approximately one-third of the way down the screen. Both xpos and ypos are measured in special units called twips. One twip is an equivalent of approximately 0.0007 inches.

The last two arguments, helpfile and context, are used in the same way as the corresponding arguments of the MsgBox function discussed earlier in this chapter.

Now that you know the meaning of the InputBox arguments, let's see some examples of using this function.

1. Add a new module to the MyFunctions (Chap04.xls) project and change the module's name to Sample8.

2. Activate the Sample8 module and enter the Informant subroutine shown below:

Sub Informant()

InputBox prompt:="Enter your place of birth:" & Chr(13) _ & " (e.g., Boston, Great Falls, etc.) "

End Sub

The above procedure displays a dialog box with two buttons. The input prompt is displayed on two lines.

Microsoft Excel

E nter _your place of birth: (e.g. Boston, Great Falls, etc.)

Cancel J

1 11

A dialog box generated by the Informant subroutine

Figure 4-10:

A dialog box generated by the Informant subroutine function in a variable. The Informant2 procedure shown below assigns the result of the InputBox function to the variable town:

Sub Informant2()

Dim myPrompt As String

Dim town As String

Const myTitle = "Enter data"

myPrompt = "Enter your place of birth:" & Chr(13) _

& "(e.g., Boston, Great Falls, etc.)" town = InputBox(myPrompt, myTitle)

MsgBox "You were born in " & town & ".", , "Your response" End Sub

Notice that this time, the arguments of the InputBox function are listed between parentheses. Parentheses are required if you want to use the result of the InputBox function later in your procedure. The Informant2 subroutine uses a constant to specify the text to appear in the title bar of the dialog box. Because the constant value remains the same throughout the execution of your procedure, you can declare the input box title as a constant. However, if you'd rather use a variable, you still can.

When you run a procedure using the InputBox function, the dialog box generated by this function always appears in the same area of the screen. To change the location of the dialog box, you must supply the xpos and ypos arguments, as explained earlier.

3. To display the dialog box in the top left-hand corner of the screen, modify the InputBox function in the Informant2 procedure, as follows:

town = InputBox(myPrompt, myTitle, , 1, 200)

Notice that the argument myTitle is followed by two commas. The second comma marks the position of the omitted default argument. The next two arguments determine the horizontal and vertical position of the dialog box. If you omit the second comma after the myTitle argument, Visual Basic will use the number 1 as the value of the default argument. If you precede the values of arguments by their names (for example, prompt:=myPrompt, title:=myTitle, xpos:=1, ypos:=200), you won't have to remember to place a comma in the place of each omitted argument.

What will happen if, instead of the name of a town, you enter a number? Because users often supply incorrect data in the input dialog box, your procedure must verify that the data the user entered can be used in further data manipulations. The InputBox function itself does not provide a facility for data validation. To validate user input, you must use other VBA instructions that are presented in the next chapter.

Converting Data Types

The result of the InputBox function is always a string. If the user enters a number, the string value the user entered should be converted to a numeric value before your procedure can use this number in mathematical computations. Visual Basic is capable of converting values that weren't possible in earlier versions of Excel from one data type to another.

1. Activate the Sample8 module in MyFunctions (Chap04.xls) project and enter the following AddTwoNums procedure:

Sub AddTwoNums()

Dim myPrompt As String Dim valuel As String Const myTitle = "Enter data" Dim mySum As Single myPrompt = "Enter a number:"

valuel = InputBox(myPrompt, myTitle, 0)

MsgBox mySum & " (" & valuel & " + 2)" End Sub

The above procedure displays the dialog box shown in Figure 4-11. Notice that this dialog box has two special features that are obtained by using the InputBox function's optional title and default arguments. Instead of the default "Microsoft Excel," the dialog box displays a text string defined by the contents of the myTitle constant. The zero entered as the default value in the edit box suggests that the user enters a number instead of text.

Once the user provides the data and clicks OK, the user's input is assigned to the variable valuel.

valuel = InputBox(myPrompt, myTitle, 0)

Figure 4-11:

To suggest that the user enter a specific type of data, you may want to provide a default value in the edit box.

The data type of the variable valuel is String. You can check the data type easily if you follow the above instruction with the statement shown below:

MsgBox varType(valuel)

valuel = InputBox(myPrompt, myTitle, 0)

Enter data


Enter a number:

OK J Cancel

1® ■

Tip 4-12: Define a Constant

To ensure that all the title bars in a particular VBA procedure display the same text, assign the title text to a constant. By following this tip, you will save yourself time typing the title text more than once.

Tip 4-13: Avoid the Type Mismatch Error

If you attempt to run the AddTwoNums procedure in previous versions of

Microsoft Excel (prior to version 2000), you will get the Type Mismatch error when Visual Basic tries to execute the following line of code:

To avoid the Type Mismatch error, use the built-in CSng function to convert a string stored in the value1 variable to a Single type number. Write the following statement:

The next line, mySum = value1 + 2, adds 2 to the user's input and assigns the result of the calculation to the variable mySum. Because the value1 variable's data type is String, prior to using this variable's data in the computation, Visual Basic goes to work behind the scenes to perform the data type conversion. Visual Basic has the brains to understand the need for conversion. Without it, the two incompatible data types (text + number) would generate the Type Mismatch error.

The procedure ends with the MsgBox function displaying the result of the calculation and showing the user how the total was derived.

0 0

Post a comment