In Excel VBA, the basic syntax is InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]). It displays a dialog and returns the user’s response as text; use Application.InputBox instead when you need Excel to restrict the kind of answer, such as a number or a cell range.
What the VBA InputBox Does
The VBA InputBox function is a simple way for a macro to ask a person for information while the macro is running. It can request a name, a file label, a quantity, or another short response. The function returns a string, so even a response that looks like a number arrives as text and may need to be checked or converted before calculations.
This is a VBA feature, not a worksheet formula. You call it from a macro or another VBA procedure; typing InputBox into a worksheet cell will not open a dialog. The dialog is also basic: it provides a prompt, a text field, and standard confirmation and cancellation buttons. For a multi-field form, use a UserForm or another data-entry approach.
Syntax and Arguments
The square brackets in the syntax indicate optional arguments; they are not typed into the VBA code. The first argument, prompt, is required and contains the message shown to the user. The remaining arguments let you add a caption, suggest a starting response, position the dialog, or associate it with a Help topic.
prompt: Required text for the message. Keep it specific enough that the user knows what to enter.title: Optional text displayed in the dialog’s title bar. If omitted, the application name is used.default: Optional text initially placed in the entry field. It is a suggestion, not a restriction.xposandypos: Optional horizontal and vertical positions for the dialog. The values are measured in twips from the relevant screen edges; if omitted, the dialog is centered.helpfileandcontext: Optional help-file name and numeric Help context. Supply them together when linking the dialog to a Help topic.
Arguments are positional in the ordinary VBA function call. If you want to provide a default but no title, you still need to leave the title position empty, as in InputBox("Enter a department:", , "Sales"). In most everyday macros, the prompt, title, and default are enough; positioning and Help arguments are less commonly needed.
A Basic VBA Example
This macro asks for a department and writes the response to cell A1 on the active worksheet. The default value appears in the text field so the user can accept it or replace it.
Sub AskForDepartment()
Dim department As String
' Ask for a department; prefill the response field.
department = InputBox("Enter a department:", "Department", "Sales")
' Write the returned text to the active sheet.
ActiveSheet.Range("A1").Value = department
End Sub
Before running a macro, save a copy of the workbook. Macro changes generally cannot be reversed with Ctrl+Z, so a backup is useful if a procedure writes to cells or otherwise changes the workbook. To keep VBA code in the file, save it in a macro-enabled format such as .xlsm; a standard .xlsx file does not preserve VBA macros.
Handling Blank Responses and Cancel
A key detail is that the VBA InputBox function returns a zero-length string when the user clicks Cancel. It also returns a zero-length string when the user confirms an empty field. Those two actions are therefore indistinguishable from the function’s return value alone. If your macro must tell cancellation apart from an intentionally blank answer, this function is not sufficient.
You can still detect whether the returned text is empty and decide how to proceed. For example, the following macro asks again until the user supplies a nonblank name. It treats Cancel like a blank response, so the loop continues; that behavior is deliberate and should be changed if cancellation ought to stop the task.
Sub AskForName()
Dim personName As String
' Repeat the prompt while the response is empty.
Do
personName = InputBox("Enter a name:", "Name")
Loop While Len(Trim$(personName)) = 0
' Use the accepted response.
ActiveSheet.Range("A1").Value = personName
End Sub
Trim$ removes leading and trailing spaces before the length check, so a response made only of spaces is treated as empty. Because this loop cannot distinguish Cancel from blank, it can keep reopening the dialog after Cancel. For a workflow where Cancel should exit cleanly, consider a different dialog or design the interaction so an empty response is an acceptable stop signal.
InputBox Versus Application.InputBox
InputBox is a VBA function. Application.InputBox is an Excel method with an additional Type argument that can constrain what Excel accepts. This distinction matters: the ordinary function always returns text, while the Excel method can return a number, Boolean value, range object, or other supported value depending on its type setting.
For example, this procedure requests a number. With Type:=1, Excel restricts the entry to a numeric response. The result is a Variant because Cancel returns the Boolean value False, while an accepted response is numeric.
Sub AskForQuantity()
Dim answer As Variant
' Request a numeric response from Excel.
answer = Application.InputBox(Prompt:="Enter a quantity:", _
Title:="Quantity", Type:=1)
' A Boolean result indicates that the user canceled.
If VarType(answer) = vbBoolean Then Exit Sub
' Store the accepted number.
ActiveSheet.Range("A1").Value = answer
End Sub
Common Type values include 0 for a formula, 1 for a number, 2 for text, 4 for a logical value, 8 for a range reference, 16 for an error value, and 64 for an array. Type 8 is useful when a macro needs the user to select cells, but handling the returned range and a cancellation requires care because the method can return different kinds of values. For a straightforward text question, the ordinary function is often simpler.
Choosing the Right Input Method
Choose based on the response the macro needs, not just on which syntax is shorter. The basic function is convenient for short text prompts and simple macros. Excel’s method is preferable when built-in type restriction can prevent an invalid entry, although your macro should still handle Cancel and check any business rules that Excel’s type setting does not cover.
- Use
InputBoxfor a short text response when blank and Cancel can be treated the same way. - Use
Application.InputBoxwhen you want Excel to request a number, formula, or range selection. - Use a UserForm when you need several fields, clearer validation messages, buttons with custom behavior, or a more guided interface.
- Use worksheet cells or a table for repeat data entry that users need to review, edit, or keep as a record.
A default value is helpful when most users will accept a common answer, but it does not validate the response. Likewise, a numeric input type does not establish that a quantity is positive, within a permitted range, or meaningful for the task. Check those conditions in the macro before using the value.
Common Problems and Compatibility
One frequent error is assuming that a number typed into the ordinary InputBox is already numeric. Its return type is String; convert it only after considering whether the response is blank and whether it is a valid number. Another common issue is overlooking cancellation: a blank result might mean either Cancel or confirmation of an empty field.
InputBox dialogs are part of desktop VBA automation. Excel for the web cannot run VBA macros, so a workbook opened there will not execute a procedure that calls either input box. Excel for Microsoft 365 on Windows supports VBA; Excel for Mac also supports VBA, though its editor and surrounding menus differ. The VBA function’s basic syntax is the same, but platform-specific macro behavior and dialog appearance may differ.
If a macro appears not to run, check that the workbook is saved in a macro-enabled format and that macros are permitted by the organization’s security settings. Do not enable macros in an untrusted workbook merely to make an input dialog appear. If a prompt opens repeatedly, inspect any loop around the call and verify that the exit condition can actually be met.
Alternatives for More Control
A UserForm is a better fit when the dialog needs multiple text boxes, drop-down lists, check boxes, explanatory labels, or custom validation. It takes more VBA setup than one line of InputBox code, but gives you control over what the user sees and how each button behaves. For a small number of fixed choices, a worksheet data-validation list can sometimes be easier for people to use and audit.
Another option is to ask users to enter information in designated worksheet cells and have the macro read those cells. This avoids repeated pop-ups and leaves a visible record, which is useful for batch entry. The trade-off is that the workbook needs a clearly marked input area and appropriate checks to prevent accidental edits or incomplete submissions.
Frequently Asked Questions
What is the correct Excel VBA InputBox syntax?
The syntax is InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]). The prompt is required; the other arguments are optional. In VBA code, omit the square brackets and separate supplied arguments with commas.
How do I set a default value in a VBA InputBox?
Put the default text in the third argument. For example, InputBox("Enter a region:", "Region", "North") displays North in the entry field. The user can replace that text, so the default does not enforce a permitted answer.
What does InputBox return when the user clicks Cancel?
The VBA InputBox function returns a zero-length string for Cancel, just as it does for a confirmed blank response. Application.InputBox instead returns the Boolean value False when canceled, which can help a macro distinguish cancellation from an accepted response.
How do I make an Excel VBA InputBox accept only numbers?
Use Application.InputBox with Type:=1 to request a number. Store the result in a Variant so the macro can also account for the Boolean False returned on Cancel, then apply any additional limits your task requires.
Does VBA InputBox work in Excel for the web?
No. Excel for the web does not run VBA macros, so a procedure that calls InputBox will not run there. Use desktop Excel for VBA, or choose a web-compatible data-entry method if users need to work in a browser.
Related
- Excel Unable to Merge Cells? Causes, Fixes, and Alternatives
- Excel Pasting: Stop Tab-Delimited Text Splitting on Paste
- Excel Text to Columns Wizard: Split Data Easily
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Check Whether a Dynamic Array Is AllocatedNo—VBA arrays are not objects, so If myArray Is Nothing is not a valid way to test one.…
- VBA Display UserForm: Methods, Examples, and FixesIn Excel VBA, display a UserForm by calling its Show method, such as frmEntry.Show. By default, the form…
- Excel VBA Date and Time Functions: A Practical GuideExcel VBA’s main current-date and time functions are Date, Time, and Now; use DateSerial and TimeSerial to build…
- VBA Copy Dictionary: Methods, Code, and Deep CopiesTo copy a VBA Scripting.Dictionary, create a second dictionary and add each key and item from the first;…
- VBA While Loop: Syntax, Examples, and Safe UseA VBA While loop repeats a block of code as long as a condition is true. In Excel,…
- Excel VBA: Find the Bottom Row of Data in a SheetTo find the bottom row in a particular Excel column, start at the worksheet’s last row and move…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.