In Excel VBA, an InputBox displays a dialog that asks the user to enter information. Use VBA’s InputBox function for a simple text response, or Application.InputBox when you need Excel to restrict the response to a type such as a number or a worksheet range.
What an Excel VBA InputBox Does
An InputBox is a small, built-in dialog for collecting one response while a macro runs. It can ask for a name, a quantity, a date-like entry, or a cell reference, for example. The macro receives the response and can then check it, use it in a calculation, or place it in a worksheet. It is not a worksheet control that stays visible after the dialog closes.
VBA provides two closely related options:
InputBoxis a VBA function that returns the user’s response as text. It is quick to use, but it does not validate that the text represents a number or a valid range.Application.InputBoxis an Excel method with aTypeargument. It can limit input to text, a number, a logical value, a range, and other supported types.
Both methods pause the macro until the user submits a response or cancels. The choice between them mainly depends on whether text-only input is sufficient or whether Excel should perform some input-type checking for you.
Where to Put and Run the VBA Code
These examples are for desktop Excel for Microsoft 365 on Windows. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. To open the Visual Basic Editor on Windows, press Alt+F11, or select Developer > Visual Basic. If the Developer tab is not shown, enable it through File > Options > Customize Ribbon and select Developer.
In the editor, choose Insert > Module, paste a complete procedure into the module, and run it with F5 while the cursor is inside that procedure. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Excel for Mac supports VBA in the desktop app; the editor can be opened from Developer > Visual Basic, and Option+F11 may also work (some keyboards require Fn). Ribbon settings and available VBA features can differ on Mac.
Use the Basic InputBox Function for Text
This standalone macro asks for a project name and writes the response to cell A1 on the active sheet. The built-in VBA function returns a string. Its arguments include the prompt, title, default response, and optional position and help information.
Sub AskForProjectName()
Dim projectName As String
' Ask for text; Cancel and a blank response both return an empty string.
projectName = InputBox(Prompt:="Enter a project name:", _
Title:="Project details", _
Default:="New project")
If Len(Trim$(projectName)) = 0 Then Exit Sub
ActiveSheet.Range("A1").Value = Trim$(projectName)
End Sub
There is an important limitation in that example: the basic InputBox function returns an empty string both when the user clicks Cancel and when the user submits a blank response. The function does not provide a separate cancellation flag. The example treats either case as a reason to stop. If your macro must distinguish those actions, use Application.InputBox and check its returned value.
Choose the Right InputBox Method
For a free-form response, the basic function is often enough. For a response that must be a particular kind of value, Application.InputBox offers useful restrictions through Type. Common values include Type:=2 for text, Type:=1 for a number, Type:=4 for a logical value, and Type:=8 for a range reference. The method returns a Variant, so the returned value can hold different kinds of data.
Excel’s type restriction is a first check, not a full set of business rules. For example, Type:=1 accepts numeric input, but it does not establish that a number is positive, whole, within a permitted range, or appropriate for a particular task. Your macro should still test requirements that matter to the workbook.
When the user cancels an Application.InputBox, the result is Boolean False. A Variant allows the macro to distinguish this Boolean from a text response or number. For text input, checking VarType is a practical way to handle cancellation separately from a submitted blank string.
Ask for Text and Handle Cancel Separately
This version uses Application.InputBox with text-only input. It exits if the user cancels, but accepts a submitted blank response; the following validation line then reports that blank as invalid. Change that rule if an empty value is meaningful in your workflow.
Sub AskForRequiredText()
Dim answer As Variant
Dim entry As String
' Type 2 requests a text response; Cancel returns Boolean False.
answer = Application.InputBox(Prompt:="Enter a department name:", _
Title:="Department", _
Default:="Sales", _
Type:=2)
If VarType(answer) = vbBoolean Then Exit Sub
entry = Trim$(CStr(answer))
If Len(entry) = 0 Then
MsgBox "A department name is required.", vbExclamation
Exit Sub
End If
ActiveSheet.Range("A2").Value = entry
End Sub
The default response can save typing, but it should be an appropriate suggestion rather than an assumed answer. A user can edit it before submitting. Also, a prompt should explain the expected format: “Enter a department name” is more useful than “Enter a value.”
Request and Validate a Number
Use Type:=1 when the response must be numeric. The example below requires a positive whole number, then stores it in A3. It checks cancellation before comparing the result with zero, since a Boolean cancellation value should not be treated as a valid count.
Sub AskForPositiveWholeNumber()
Dim answer As Variant
Dim itemCount As Long
' Type 1 asks Excel to accept numeric input.
answer = Application.InputBox(Prompt:="Enter a positive whole number:", _
Title:="Item count", _
Type:=1)
If VarType(answer) = vbBoolean Then Exit Sub
If answer <= 0 Or answer <> Fix(answer) Then
MsgBox "Enter a whole number greater than zero.", vbExclamation
Exit Sub
End If
itemCount = CLng(answer)
ActiveSheet.Range("A3").Value = itemCount
End Sub
Fix returns the integer portion of a number, so comparing the response with Fix(answer) rejects fractional values. The positive check rejects zero and negative entries. If your task permits zero or decimals, adjust those checks rather than relying on the InputBox type alone. For extremely large values, also check that the number falls within the range that a VBA Long can store before converting it.
Let the User Select a Worksheet Range
Application.InputBox with Type:=8 asks the user to select a range. The response is an object reference, so assign it to a Range variable using Set. The returned Variant makes it possible to identify Cancel before assigning the range.
Sub AskForSourceRange()
Dim answer As Variant
Dim sourceRange As Range
' Type 8 asks the user to select a range on a worksheet.
answer = Application.InputBox(Prompt:="Select the cells to use:", _
Title:="Choose source cells", _
Type:=8)
If VarType(answer) = vbBoolean Then Exit Sub
Set sourceRange = answer
MsgBox "Selected range: " & sourceRange.Address(External:=True)
End Sub
The user can usually click or drag on a sheet while the dialog is open to make the selection. A range can contain multiple cells, so decide whether your macro should accept a multi-cell selection or require one cell. For instance, a macro expecting a single input cell should check sourceRange.Cells.CountLarge before proceeding. The range may also be on a different worksheet from the active one; use the range’s own worksheet reference when the operation depends on where it was selected.
Prompts, Defaults, and Useful Design Choices
A well-designed prompt prevents avoidable errors. Keep it specific, explain units or formatting, and say whether the user should enter one value or select cells. The title argument can identify the task, while the default argument can show a typical or previously used response. Optional arguments such as position can place the dialog, but a standard position is usually adequate.
- For dates, say what format is expected, and validate the resulting value before using it. Text entry and regional date conventions can make ambiguous dates difficult to interpret consistently.
- For choices from a short fixed list, an InputBox is less helpful than a controlled selection interface. A user can mistype a choice even when the prompt lists valid options.
- For values used repeatedly, consider reading a setting from a worksheet or building a UserForm rather than prompting separately for every item.
- Keep the response in a variable, validate it, and only then write to cells or use it in later steps. This makes it easier to handle cancellation and incorrect entries cleanly.
An InputBox is intentionally brief: it collects one response at a time and does not offer the richer layout, labels, and controls of a UserForm. If a macro needs several related entries, a form can make the workflow clearer and allow validation before the user submits the group of fields.
Common Problems and How to Avoid Them
- Cancel looks like blank text: This is the key limitation of VBA’s
InputBoxfunction. UseApplication.InputBoxif the macro must tell a cancellation apart from an empty response. - A number arrives as text: The basic function returns text even if the user typed digits. Convert only after checking that the content is valid, or use
Application.InputBoxwithType:=1. - The macro stops with a type mismatch: A canceled dialog, an unexpected value, or an invalid conversion can cause problems if the response is used without checking it. Inspect the return value before assigning it to a more restrictive variable.
- The wrong worksheet receives the value:
ActiveSheetdepends on which sheet is active when the code runs. For a specific worksheet, qualify the reference, for exampleThisWorkbook.Worksheets("Inputs").Range("A1"), and ensure that sheet exists. - The macro cannot be run in a browser: Excel for the web does not run VBA. Open the file in desktop Excel to use VBA, and keep a macro-enabled file format when saving code.
Input validation is not a substitute for safe macro design. Do not use user-entered text as if it were trusted code, and avoid making destructive changes before the response has passed validation. A confirmation dialog can help before a consequential operation, but it does not replace keeping a backup of important workbooks.
Frequently Asked Questions
How do I create an InputBox in Excel VBA?
In a VBA procedure, assign the result of InputBox("Your prompt") to a variable. Use Application.InputBox instead when you need Excel to request a specific kind of response, such as a number or range.
How do I detect Cancel in an Excel VBA InputBox?
The basic InputBox function returns an empty string for both Cancel and a submitted blank answer, so it cannot distinguish them. With Application.InputBox, store the result in a Variant and check whether VarType(result) = vbBoolean; Cancel returns Boolean False.
How do I make an InputBox accept only numbers?
Call Application.InputBox with Type:=1. That requests numeric input, but you must still check any further requirements, such as whether the value must be positive or a whole number.
Can an Excel VBA InputBox select cells?
Yes. Use Application.InputBox with Type:=8, check for cancellation, and assign the returned range object to a Range variable with Set.
Does VBA InputBox work in Excel for the web?
No. VBA macros do not run in Excel for the web. Use desktop Excel for Windows or Mac to run VBA; if the workbook must work in a browser, consider a non-VBA approach such as worksheet cells or Office Scripts where available.
Related
- Import a CSV into an Existing Excel Workbook
- How to Compare Two Excel Sheets and Find Every Change
- How to Compare Two Excel Sheets
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Remove Item From Dictionary: Methods and ExamplesTo remove one entry from a VBA dictionary, call dict.Remove key, where key is the entry’s key—not its…
- Excel VBA Activate Sheet: Syntax, Examples, and FixesTo activate a worksheet with VBA, reference it by name and call its Activate method: ThisWorkbook.Worksheets("Summary").Activate. The sheet…
- Copy Paste Values in Excel VBA: Methods and ExamplesTo copy a range’s results—not its formulas—in Excel VBA, use Copy followed by PasteSpecial Paste:=xlPasteValues. For a quick…
- How to Copy and Paste Formulas with VBA in Excel SafelyTo copy a formula with VBA in Excel, copy its source cell and use PasteSpecial Paste:=xlPasteFormulas on the…
- Macro Recorder Software: How It Works in ExcelMacro recorder software captures a sequence of actions and lets you replay them, reducing repetitive work. In Microsoft…
- Word VBA Mail Merge: How to Automate ItWord VBA can automate a mail merge by telling a prepared Word merge document to create a new…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.