Microsoft Office Tutorials and References
In Depth Information
Chapter 12: Custom Dialog Box Alternatives
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 (or a named cell or range) 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-3). The user can either type the range address manually or
use the mouse to select the range in the sheet.
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
Dim UserRange As Range
On Error GoTo Canceled
Set UserRange = Application.InputBox _
(Prompt:=”Range to erase:”, _
Title:=”Range Erase”, _