The Excel Input Box method

Using Excel's InputBox method offers three advantages over VBA's InputBox function:

■ You can specify the data type returned.

■ The user can specify a worksheet range by dragging in the worksheet.

■ Input validation is performed automatically. The syntax for the Excel InputBox method is:

InputBox(Prompt [,Title] [,Default] [,Left] [,Top] [, HelpFile, HelpContextID] [,Type]

■ Prompt : Required. The text displayed in the input box.

■ Title : Optional. The caption in the input box window.

■ Default : Optional. The default value to be returned by the function if the user enters nothing.

■ Left , Top : Optional. The screen coordinates of the upper-left corner of the window.

■ HelpFile , HelpContextID : Optional. The help file and help topic.

■ Type : Optional. A code for the data type returned, as listed in Table 12-1 .

A formula 1

A number 2

A string (text) 4

A logical value (True or False ) 8

A cell reference, as a range object 16

An error value, such as #N/A 64

An array of values

Table 12-1: CODES TO DETERMINE THE DATA TYPE

RETURNED BY EXCEL'S INPUTBOX METHOD ^ Open table as spreadsheet

Code

Meaning

Excel's InputBox method is quite versatile. To allow more than one data type to be returned, use the sum of the pertinent codes. For example, to display an input box that can accept text or numbers, set type equal to 3 (that 1 + 2, or number plus text ). If you use 8 for the type argument, the user can enter a cell or range address manually or point to a range in the worksheet.

The EraseRange procedure, which follows, uses the InputBox method to allow the user to select a range to ere (see Figure 12-3 ). The user can either type the range address manually or use the mouse to select the range in the sheet.

ti 1

p

H

1

ai

«

a

j;

ri

1"»

«i

if

t

ring* ti*ih

l y

a

11

4

U

Cl

n

1

a

M

t

B

if

M

X

it

Si

91

«i

n

31

il

il

n

ar

1

fit

M

It

>1«

it

11

a

T

B.

m

m

Ji

Ü

M

»

•î

!

*

19

Si

U

1

M

u

11

1

rt

H

et

H

J1

u

n

M

B

17

ID

n

H

7<

il.

jn

11

il

wj

M

78

II

i:

IS

H

!T

(I

Si

JS

M

1J

n

31

n

74

H

u

M

41

IJ

H

ü

S

t

II

M

JJÎ

Hi

17

14

M

M

9

Mi

W

1

i>;

«4

97

r

M

a

11

Lt

J9

«1

m

9

ÎT

it

Si

J4

i-6

il

¿i

H

a

M

M

U

17

«

-a

Si

J1

H

IT

u

TS

t:

I*

n

14

(9

m

JS

»

H

u

20

»

smtL

*

Figure 12-3: Using the InputBox method to specify a range.

Figure 12-3: Using the InputBox method to specify a range.

The InputBox method with a type argument of 8 returns a Range object (note the Set keyword). This range is then erased (by using the Clear method). The default value displayed in the input box is the current selection's address. The On Error statement ends the procedure if the input box is canceled.

Sub EraseRange ()

Dim UserRange As Range On Error GoTo Canceled

Set UserRange = Application.InputBox _ (Prompt:="Range to erase:", _ Title:="Range Erase", _ Default:=Selection.Address, _ Type:=8) UserRange.Clear UserRange.Select Canceled: End Sub

CD-ROM

This example is available on the companion CD-ROM in a file named % inputbox method.xlsm .

Yet another advantage of using Excel's InputBox method is that Excel performs input validation automatically. In the GetRange example, if you enter something other than a range address, Excel displays an informative message and lets the user try again (see Figure 12-4 ).

Ulffdwlh Offlir c E tit-ri tt*i"e<btnqi- K'j l*î-nS + toi vjad, & tîpl-<sd'«si if»nr*ir.i <r*twt*i**ft d»* m^été

. rife-Btr c s* *** »'tier's, nr rire-; 6-rajçh -t-r mi mtV. Is xfect.

Figure 12-4: Excel's InputBox method performs validation automatically.

0 0

Post a comment