VBA · User interaction
Application.InputBox for ranges
An InputBox that lets the user select a range with the mouse.
When to use it
Application.InputBox is the typed version of InputBox: Type:=8 returns a Range the user selects with the mouse, Type:=1 forces a number.
The code
- Code
Set rng = Application.InputBox("Select the data", Type:=8)
Worked examples
Ask for a range
Set rng = Application.InputBox("Select the data", Type:=8) → The user clicks a range and it comes back as a Range object
The best way to ask for a range.
Ask for a number
n = Application.InputBox("How many?", Type:=1) → A number, with Excel rejecting text
Validation built in.
Handle Cancel
On Error Resume Next Set rng = Application.InputBox("Select", Type:=8) On Error GoTo 0 If rng Is Nothing Then Exit Sub → Handles Cancel, which raises an error for Type 8
Cancel handling.
Worth knowing
- Type codes: 0 formula, 1 number, 2 text, 4 logical, 8 range, 64 array.
- The user can type an address or click.
- Combine types by adding: 1 + 2 accepts numbers or text.
Where it goes wrong
- Cancel with Type 8 raises an error rather than returning Nothing; wrap it.
- Cancel with Type 1 returns False, which is 0.
Related
Learn the moves here — or let Wauvel run them on your numbers.
$99/mo, everything included. Free for 14 days, no card.
One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.