VBA InputBox in Excel — The Two InputBoxes & When to Use Each

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-06

TL;DR — Excel gives you two functions called InputBox. The plain one returns text; the Excel one can demand a number, a date, or even let the user drag-select a range:

' 1. VBA InputBox — always returns a String
Dim name As String
name = InputBox("Your name?", "Setup")

' 2. Application.InputBox — Type:=1 forces a number, Type:=8 grabs a range
Dim qty As Variant
qty = Application.InputBox("How many?", "Quantity", Type:=1)

Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select the data:", "Range", Type:=8)
On Error GoTo 0

Choosing the wrong one is why so many InputBox macros need a hand-written validation loop they don't actually need. Here's how to pick.

The mental model: same name, two different tools

Type InputBox in the editor and VBA offers it twice — that's not a glitch. There genuinely are two:

• InputBox(...) — the VBA language function. Dead simple, works in any Office app, and always hands back a String. Whatever the user types, you get text.
• Application.InputBox(...) — an Excel-only method with one extra superpower: a Type argument. It can insist on a number, a date, a logical, or a cell range, and it returns that typed value — not a string you have to convert.

If MsgBox is the intercom that only broadcasts, InputBox is the one place a quick macro can receive a single typed value without building a whole UserForm. Pick the plain one for free text; pick Application.InputBox the moment the answer should be a number, a date, or a range.

Picking by the answer you need

' Free text → plain InputBox is shortest
Dim note As String
note = InputBox("Add a comment:")

' A number you'll do maths on → Application.InputBox Type:=1
Dim rate As Variant
rate = Application.InputBox("Interest rate %:", Type:=1)

' A cell range the user points at with the mouse → Type:=8 (the killer feature)
Dim target As Range
On Error Resume Next
Set target = Application.InputBox("Pick the cells to total:", Type:=8)
On Error GoTo 0

That Type:=8 range picker is the one thing no other lightweight dialog can do — the user drags across the sheet and you get a real Range object back. Reaching for a UserForm with a RefEdit control just to grab one range is overkill when this exists.

The Type values worth knowing

You can add them for "either/or" — Type:=1 + 2 accepts a number or text. The point of Type is that Excel validates the input for you: type letters into a Type:=1 box and Excel rejects it before your code ever runs. That's the validation loop you were about to write — already built in.

The trap that breaks both: detecting Cancel

This is where most InputBox bugs live, and it's different for each version.

Plain InputBox returns an empty string "" when the user clicks Cancel — and also when they leave it blank and click OK. You cannot tell the two apart:

Dim s As String
s = InputBox("Enter a code:")
If s = "" Then Exit Sub      ' treats Cancel and "empty OK" the same — usually fine

Application.InputBox returns the Boolean False on Cancel. The catch: you must catch it before assigning to a typed variable, so store it in a Variant first:

Dim v As Variant
v = Application.InputBox("How many rows?", Type:=1)
If VarType(v) = vbBoolean Then Exit Sub    ' user cancelled
' now safe to use v as a number
Range("B1").Value = v

⚠️ Do not write If v = False Then Exit Sub here. If the user legitimately enters 0, then v = False is also true (VBA treats 0 as False), and your macro quits on valid input. Testing VarType(v) = vbBoolean is the only reliable check — a real number comes back as vbDouble, never vbBoolean. For a Type:=8 range, use the Is Nothing pattern from the TL;DR instead.

Stop hand-rolling validation loops

Because Application.InputBox already enforces the type, the classic "keep asking until they enter a valid number" loop is mostly wasted code:

' ❌ The loop people write with plain InputBox
Dim s As String
Do
    s = InputBox("Enter a number:")
Loop Until IsNumeric(s) And s <> ""

' ✅ Let Excel enforce it
Dim n As Variant
n = Application.InputBox("Enter a number:", Type:=1)
If VarType(n) = vbBoolean Then Exit Sub

Rule of thumb: if you find yourself validating the kind of value, you picked the wrong InputBox — switch to Application.InputBox with the right Type. Validation of the meaning (in range? already exists?) is still yours to do.

When InputBox stops being the right tool

One value, two at a push — InputBox chains are fine. Beyond that they fall apart: ask for five things in a row and the user who fudges question two has no way back, no way to review, no Cancel-the-whole-thing. That's the signal to graduate to a UserForm, where every field is visible at once and editable until they hit OK. Don't reach for it earlier than you need to — three plain text fields with no validation are perfectly happy as three InputBoxes.

Common InputBox mistakes (and the fix)

One prompt is easy — the logic after it isn't

Grabbing a number or a range with InputBox is the simple part. The work is what happens next — "for the range they picked, sum each column, skip blanks, and flag anything above the average." ExcelMaster Agent lets you describe that whole operation in plain English and it writes the macro — prompt, range handling, and all. Try it free →

Related guides

• VBA MsgBox in Excel — Yes/No, Buttons & the Brackets Rule
• VBA UserForm in Excel — Build a Real Data-Entry Form
• VBA Range in Excel — Reference, Read & Write Cells
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

What is an InputBox in VBA? An InputBox is a dialog that prompts the user to type a value and returns it to your macro. VBA actually has two: the plain InputBox function (always returns a String) and Application.InputBox (Excel-only, with a Type argument that can return a number, date, or range).

What is the difference between InputBox and Application.InputBox? InputBox always returns text, works in any Office app, and is the shortest option. Application.InputBox is Excel-only and adds a Type argument so it can enforce and return a number (Type:=1), a range the user selects with the mouse (Type:=8), and more. It returns False when cancelled.

How do I get a number from a VBA InputBox? Use Application.InputBox("Prompt", Type:=1) and store the result in a Variant. Excel will reject non-numeric entries automatically. Check for Cancel with If VarType(v) = vbBoolean Then Exit Sub before using the number.

How do I detect Cancel in a VBA InputBox? Plain InputBox returns an empty string "" on Cancel (indistinguishable from a blank OK). Application.InputBox returns False; store it in a Variant and test VarType(v) = vbBoolean. For a range (Type:=8), wrap the call in On Error Resume Next and test If rng Is Nothing.

How do I let the user select a range with InputBox? Use Application.InputBox("Select cells:", Type:=8) and assign with Set. Wrap it in On Error Resume Next … On Error GoTo 0 so a Cancel doesn't raise a type-mismatch error, then check If rng Is Nothing Then Exit Sub.