The Excel Input Box method

Using Excel's InputBox method (rather than VBA's InputBox function) offers three advantages:

♦ 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 object.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 at 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.

Table 12-1

CODES TO DETERMINE THE DATA TYPE RETURNED BY EXCEL'S INPUTBOX METHOD

Code

Meaning

0

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

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 is, 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 erase (see Figure 12-2). The user can either type the range address manually or use the mouse to select the range in the sheet.

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 cancelled.

Sub EraseRange()

Dim UserRange As Range

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

C

n

F

H I

J

7

60

16

88

24

80

251 85

40

85

72

8

56

7

38

3

96

29 86

11

78

39

9_

68

_14

41

65

29

94

65

37

6

37

38 j 23 17 83! 77 76

87 33

39

85 44

13

9Î 6 13

49

52

37

14

41

5

66

99

17

2

15

11. 18 29

91

41

30

16

56

17

m 84

R

57

1H

69

27

78

39

92

9

57

8

19

76

I OK

I Cancel |

78

28

?n

88

54

4

5

n

18

21

78

14

22

92

27

46

79

28

13

18

2

17

37

27

65

62

47

94

1C

71

28

23

66

33

65

26

28

14

60

85

86| 61

24

19

85

90

30

75

43

22

81

n

18

25

17

45

51

36

7

71

36

2

78

42

-

M ► MIX Sheet 1/

J±l

_M

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

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

This example is available on the companion CD-ROM.

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-3).

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

Post a comment