The Application.InputBox Method In Excel VBA

The Application.InputBox() method provides better input control and overall more options than the basic InputBox(). Its return value depends on the type specified. You can choose the input type to be a number, formula, cell reference, text, or Boolean value. If the user clicks Cancel, the method returns False, which you can use to control the flow of your program.

Like the simple input box, you can specify a default value and a title. The input type is set using the last parameter. Therefore, the examples below use named parameters for clarity.

Entering a Number

Here is an example where a number is requested (Type 1):

Sub EnterNumber()
    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("A1").Value = Application.InputBox(Prompt:="Number:", Type:=1)
End Sub

Explanation:
You can run the EnterNumber() procedure through the Macro dialog box, accessible via the Excel ribbon under the View tab → Macros → View Macros. Alternatively, you can create a button on the worksheet and link it to this procedure.

If the user inputs a valid number, it is placed in cell A1. Valid examples include « 5.7 », « 5e2 » (which equals 5 × 10² = 500), or « 0.57 ». Invalid examples are « 5a7 » or « a57 ». Note that entering « 5.7 » might be interpreted as the date July 5 of the current year depending on regional settings.

If the user inputs an invalid number, a warning message appears, and the input box remains open until a valid number is entered, as shown in Figure 8.5.

Entering a Formula

An example to input a formula (Type 0):

Sub EnterFormula()
    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("A1").Value = Application.InputBox(Prompt:="Formula:", Type:=0)
End Sub

Explanation:
Run EnterFormula() via the Macro dialog or a button. The user can enter formulas including cell references, which may also cover non-contiguous ranges.

Typing =SUM($A$10:$A$12) will insert this formula into cell A1, showing the calculated result. Pay attention to the use of absolute references with dollar signs.

If the user starts typing =SUM(, they can select the cells using the mouse or arrow keys before closing the parenthesis.

Entering an invalid formula results in an error message.

Entering a Cell Range

An example for entering a cell range (Type 8):

Sub EnterRange()
    Dim rng As Range
    ThisWorkbook.Worksheets("Sheet1").Activate
    Set rng = Application.InputBox(Prompt:="Cell range:", Type:=8)
    rng.Borders.LineStyle = xlContinuous
    Set rng = Nothing
End Sub

Explanation:
Run EnterRange() through the Macro dialog or a button. First, an object variable for a Range object is declared.

The user can input ranges by typing or selecting with the mouse. Non-contiguous ranges are also supported. Remember to use absolute references with dollar signs for non-contiguous areas.

The selected range is returned and stored in the object variable, allowing further actions such as formatting or calculations.

Additional Notes

Besides the types described above—

• 1 for numbers,
• 0 for formulas, and
• 8 for ranges,

there are other type codes available, for example:

• 2 for text input, and
• 4 for Boolean values.