Inputbox function usage

Inputbox functions and msgbox functions are frequently used in simple interaction with users.

I. inputbox Functions

1. Role

A text box is displayed, prompting you to enter text, numbers, or select a cell area in the text box. When you press OK, a string containing the text box content is returned.

2. Syntax

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

Parameter description:

(1) prompt, a required parameter. It is a string that prompts information in the input box. Its maximum length is about 1024 characters, which is determined by the width of the characters used. If the prompt contains multiple rows, you can use a combination of carriage return (CHR (13), line feed (CHR (10), or carriage return linefeed (CHR (13) between each row) & CHR (10.

(2) Title, an optional parameter, used as a string in the title bar of the input box. If this parameter is omitted, the application is displayed in the title bar.ProgramName.

(3) Default: an optional parameter, which is used as the default string in the input box. It is used as the default value when no other input is available. If this parameter is omitted, the text box is blank.

(4) xpos, an optional parameter, which is a numerical value and appears in pairs. It specifies the horizontal distance between the left side of the input box and the left side of the screen. If this parameter is omitted, the input box is centered horizontally.

(5) ypos, an optional parameter, which is a numerical value and appears in pairs. It specifies the distance between the top of the input box and the top of the screen. If this parameter is omitted, the input box is about 1/3 vertical from the bottom of the screen.

(6) helpfile, an optional parameter. It is a string that indicates a Help file. It is used to provide context-related help for the input box. If helpfile exists, context is required.

(7) context, an optional parameter, which is a numerical value and the context number of a help topic in the Help file. If context exists, helpfile must exist.

3. Description

(1) If the helpfile and context parameters are provided at the same time, you can press the F1 key to view the Help topics corresponding to the context. In Excel, a help button is automatically added to the input box.

(2) If you click OK or press enter, the inputbox function returns the content in the text box. If you click Cancel (cancel ), then this function returns a zero-length string ("").

(3) If you want to enter multiple parameters and omit some parameters in the middle, you must add a comma (,) separator to the corresponding position.

(4) The inputbox function returns a string. To obtain a value, use the Val function to convert the string to a value.

Ii. inputbox Method

1. Role

A dialog box for receiving user input is displayed, and the information entered in this dialog box is returned. The inputbox method can be used to specify the data type to be returned. You can drag and drop a worksheet's cell area in the worksheet and automatically perform input validity verification.

2. Syntax

Object. inputbox (prompt, title, default, left, top, helpfile, helpcontextid, type)

Parameter description:

(1) Prompt, required parameter, string type, indicates the text in the input box, can be a string, number, date, or a Boolean value (Excel automatically converts the value to string type ).

(2) title. An optional parameter. It is the title of the variant type input box. If this parameter is omitted, the default title is used.

(3) Default: an optional parameter. It is of the variant type and specifies the default value of the text box when no content is entered. If this parameter is omitted, the content in the text box is blank. This value can be a range object.

(4) left, an optional parameter. It is of the variant type. Specifies the X coordinate of the input box window relative to the upper left corner of the screen.

(5) Top (optional parameter): variant type. Specifies the Y coordinate of the input box window relative to the upper left corner of the screen.

(6) helpfile, an optional parameter. It is of the variant type. The Help file name used in this input box. If the helpfile and helpcontextid parameters exist, a help button is displayed in the input box.

(7) helpcontextid, an optional parameter. It is of the variant type. Help topic.

(8) type, an optional parameter. It is of the variant type and specifies the type of returned data. If this parameter is omitted, the input box returns text. The data type can be the sum of one or more of the following tables.

If appropriateCodeReturns multiple data types. To display an input box that can accept text or numbers, set type to 3 (that is, the sum of 1 and 2, or add text to a number). If type is set to 8, you can enter a cell or cell area, or use the mouse to select a cell area in the worksheet.

Data Type code table returned by the inputbox Method
Code

Description

0

Formula

1

Number

2

String (text)

4

Logical value (true or false)

8

Cell reference, which is a range object

16

Error value, for example, # N/

64

Numeric Value

3. Description

(1) You can use inputbox to display a simple dialog box to enter the information required by the macro. This dialog box has a "OK" button and a "cancel" button. If the "OK" button is selected, inputbox returns the value entered in the dialog box. If you click the cancel button, the value of inputbox is false.

(2) If the type is 0, inputbox returns the input formula in text format. For example, "= 2 * Pi ()/360 ". If there is a reference in the formula, A1-style reference will be returned (use the convertformula method to convert the reference style ).

(3) If type is 8, inputbox returns a range object. The Set statement must be used to specify the result to a range object, as shown in the following example.

Set myrange = application. inputbox (Prompt: = "sample", type: = 8)

If you do not use the set statement, this variable is set to the value of this region, rather than the range object itself.

(4) If you are required to enter a formula using the inputbox method, you must use the formulalocal attribute to specify this formula to a range object.

(5) The inputbox method differs from the inputbox function in that the inputbox method can automatically verify the input validity of user input, and can also be used for inputting Excel objects, error values, and formulas. Note that

Application. inputbox

The inputbox method is called without an object identifier.

Inputbox

The inputbox function is called.

4. Example

(1)

Prompt users to enter numbers

Mynum = application. inputbox ("enter a number ")

(2)

The user is prompted to select a cell in sheet1 and use the type parameter to return a valid cell reference (a range object ).

Worksheets ("sheet1"). Activate

Set mycell = application. inputbox (Prompt: = "select a cell", type: = 8)