Microsoft Office Tutorials and References
In Depth Information
Table 16-5. Values for the InputBox Method's Type Parameter
The name of the Help file and the context ID for a help topic to invoke when the user hits
the Help button on the input box. If these arguments are omitted, then no Help button is
included on the input box dialog.
Type
The data type that can be entered into the text box by the user (and thus the return type of
the method). It can be one or a sum of the values in Table 16-5 . When the value is a sum
of several numbers, then any of the corresponding data types is acceptable. It follows that
formulas are always acceptable. The default value is 2 for Text.
Tab le 16-5. Values for the InputBox Method's Type Parameter
Value
Meaning
0
A formula
1
A number
2
Text (a string)
4
A logical value (True or False)
8
A reference to a single cell
16
An error value, such as #N/A
64
An array of values
Unfortunately, the type checking done by the InputBox method does not seem to be very accurate.
To illustrate, the InputBox statement:
Range("A1").Value = Application.InputBox( _
Prompt:="Enter data", Type:=0)
should accept only formulas and not text. However, entering the text "test" simply puts this text in
cell A1. (The help documentation does say that when Type is 0, InputBox returns the formula as
text and any references in the formula are returned as A1-style references.)
Note that when Type is equal to 8, the InputBox method returns a Range object that refers to the
cell in the reference. Therefore, we must use the Set statement to assign this object to a variable
of type Range, as in:
Dim rng as Variant
Set rng = Application.InputBox( _
Prompt:="Enter Cell Reference", Type:=8)
If we omit the Set statement, the variable is set to the value in the range, rather than the Range
object itself. (If we had declared the rng variable to be of type Range, then the preceding code,
without the Set statement, would result in the error message, "Object variable or With block
variable not set.")
When Type is equal to 64, the user is expected to enter a rectangular cell range that will be treated
as a two-dimensional array. For instance, consider a worksheet as shown in Figure 16-3 .
Figure 16-3. Illustration of Type = 64
 
 
Search JabSto ::




Custom Search