A VBA InputBox example can ask for a value, store the reply, and use it in a macro. For a simple text prompt, assign the result of InputBox to a declared String variable, then check whether the user entered anything before continuing.
Before running any macro, save a separate copy of the workbook: VBA macros cannot be undone with Ctrl+Z. The example below treats a blank reply and Cancel alike, which is appropriate when either means “do not continue.”
Sub AskForName()
Dim userName As String
' Ask for a name; the second argument is the dialog title.
userName = InputBox("What is your name?", "Name")
' A blank reply or Cancel produces an empty string.
If Len(Trim$(userName)) = 0 Then
MsgBox "No name was entered.", vbInformation
Exit Sub
End If
MsgBox "Hello, " & userName & "!", vbInformation
End Sub
What VBA InputBox Does
InputBox is a built-in Visual Basic for Applications function that displays a small dialog with a prompt, a text field, and OK and Cancel buttons. It returns the text the user submits. In Excel, a macro can then use that text to choose a worksheet, request a label, or gather a simple value without placing a form on the sheet.
Despite the name, the standard VBA InputBox does not validate the kind of data entered. The user can type a number, a word, or punctuation; the function returns a String. If you need Excel to enforce a number, date, or range selection, use a validation step or consider Application.InputBox, which has a Type argument.
Where to Put and Run the Example
In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure into the module window, and run it from the editor with the cursor inside the procedure by pressing F5. You can also run a public macro through Developer > Macros. If the Developer tab is hidden, turn it on through File > Options > Customize Ribbon.
On Excel for Mac, open the Visual Basic Editor from the Developer tab, or use Option+F11 (on some keyboards, Fn+Option+F11). The precise keyboard behavior depends on the Mac and its function-key settings. The VBA prompt itself works in desktop Excel on Windows and Mac, although some ribbon locations and editor controls differ. Excel for the web cannot create, run, or edit VBA macros; open the workbook in the desktop application instead.
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the macro. A normal .xlsx file does not store VBA code; saving to that format removes the macro. Macro security settings may also prevent code from running, so only enable macros in files from sources you trust.
Understanding the InputBox Arguments
The standard function accepts optional arguments in this order: Prompt, Title, Default, Xpos, Ypos, HelpFile, and HelpContextID. In everyday macros, the prompt is essential; a title and default value make the dialog clearer. The position and help arguments are rarely needed for a basic request.
For example, InputBox("Enter a department code:", "Department", "SALES") displays “Enter a department code:” as its instruction, “Department” in the title bar, and “SALES” in the text field initially. The user can keep or replace the default. A default is only a starting suggestion; it does not restrict what can be entered.
Use named arguments when a call skips optional arguments. For instance, specifying Title:= and Default:= makes the purpose of each value easier to read and avoids accidentally putting a title in the prompt position. Keep the prompt concise and include the expected format, such as “Enter a whole number from 1 to 12.”
Check and Convert the User’s Reply
Because InputBox returns text, validate the reply before using it as a number or date. IsNumeric can test whether a string can be interpreted as numeric, but it does not enforce a business rule such as “must be a positive whole number.” Convert only after checking, and then check the permitted range.
This example requests a quantity from 1 through 100. It exits for a blank reply or Cancel, reports invalid input, and converts a valid value to a Long integer:
Sub AskForQuantity()
Dim reply As String
Dim quantity As Long
' Save a copy of the workbook before running this macro.
reply = InputBox("Enter a whole number from 1 to 100:", _
"Quantity")
' Standard InputBox uses an empty string for Cancel or blank input.
If Len(Trim$(reply)) = 0 Then Exit Sub
If Not IsNumeric(reply) Then
MsgBox "Enter a number, such as 12.", vbExclamation
Exit Sub
End If
If CDbl(reply) < 1 Or CDbl(reply) > 100 _
Or CDbl(reply) <> Fix(CDbl(reply)) Then
MsgBox "Enter a whole number from 1 to 100.", vbExclamation
Exit Sub
End If
quantity = CLng(reply)
MsgBox "Quantity entered: " & quantity, vbInformation
End Sub
The range check also rejects decimal values, while IsNumeric alone would accept them. Numeric interpretation can depend on the computer’s regional settings, particularly for decimal separators. If the value has a strict format, explain that format in the prompt and test it explicitly rather than assuming every user’s locale reads numbers the same way.
InputBox Versus Application.InputBox
In Excel VBA, Application.InputBox is a method with a Type argument that can restrict the kind of response. Common Type values are 1 for a number, 2 for text, 4 for a logical value, and 8 for a worksheet range. This can reduce invalid entries, but your code still needs to handle cancellation and any rules specific to the task.
With Type:=1, the method returns a number when the user accepts a valid numeric entry, and returns the Boolean value False when the user clicks Cancel. The return value is a Variant, so check its type before treating it as a number. This avoids mistaking a canceled dialog for a value:
Sub AskForNumber()
Dim answer As Variant
' Save a copy of the workbook before running this macro.
answer = Application.InputBox( _
Prompt:="Enter a number:", _
Title:="Number", _
Type:=1)
' Cancel is Boolean False; an accepted number is numeric.
If VarType(answer) = vbBoolean Then
If answer = False Then Exit Sub
End If
MsgBox "You entered " & CStr(answer), vbInformation
End Sub
Use the standard function when a short text answer is enough and your code can validate it. Choose Application.InputBox when the user should provide a particular data type, especially a worksheet range. For a Type 8 range, the result must be assigned to an object variable with Set; cancellation needs careful handling because the method’s result is a Variant and a range result is an object. Range-selection code is therefore a more involved example than a basic text prompt.
Common Problems and Safer Prompt Design
- Cancel looks like blank input: The standard
InputBoxreturns an empty string both when the user clicks Cancel and when they submit an empty field. If those actions need different meanings, use another interface or a design that can distinguish them. - Unexpected text reaches later code: Do not assume users follow the prompt. Check for blank input, allowed values, valid ranges, and expected formats before changing cells or calling other procedures.
- A default value is mistaken for validation: The Default argument pre-fills the field; it does not force the user to retain that value or limit the response.
- The dialog is unclear: Ask for one piece of information at a time. Include units, a range, or a sample where useful. “Enter a date (for example, 3/15/2026)” is clearer than “Date?”
- A macro appears missing or disabled: Confirm that the file is saved as
.xlsm, that the code is in a standard module when appropriate, and that the workbook was opened in desktop Excel with macros permitted.
Input boxes are best suited to quick, low-risk interactions. Avoid prompting repeatedly for dozens of records; a worksheet table or form is easier to review and less frustrating. Also, do not treat a prompt as a security boundary. A user can supply unexpected text, so macros that build formulas, file paths, or commands from an answer need appropriate validation and safe handling.
When to Use a Form or Another Input Method
A simple InputBox is useful for a one-off name, code, or choice. For more complex data entry, a UserForm can show labeled controls, dropdown lists, validation messages, and multiple fields together. It takes more setup, but it can make a repeated workflow easier to understand and can reduce spelling differences when users choose from a controlled list.
If the requested value belongs in a worksheet, direct cell entry with Excel data validation may be more transparent than a hidden prompt. For a macro that processes a selected range, letting the user select the cells on the sheet or using Application.InputBox with Type 8 can be more natural than asking them to type an address. Choose the least complicated method that makes the expected input and its consequences clear.
Frequently Asked Questions
How do I make a VBA InputBox with a default value?
Pass the suggested text as the third argument: InputBox("Enter a code:", "Code", "A100"). The value appears in the field but remains editable.
How can I tell if the user clicked Cancel?
With the standard VBA InputBox, you generally cannot distinguish Cancel from submitting a blank field: both return an empty string. Application.InputBox returns Boolean False on Cancel, so inspect the returned Variant’s type when using it.
Can InputBox accept only numbers?
The standard InputBox always returns text, so test and convert the answer in your code. Excel’s Application.InputBox supports Type:=1 for numeric input, but you should still check cancellation and any required range or whole-number rule.
Does VBA InputBox work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to use VBA InputBox.
Can I undo a macro that uses InputBox?
Usually not with Ctrl+Z. Running VBA clears Excel’s normal undo history, and macro changes cannot generally be reversed through Undo. Save a separate copy before running code that changes workbook data.
Related
- How to Paste an Image in Excel: Steps, Tips, and Fixes
- How to Paste Into Filtered Cells in Excel
- How to Paste Filtered Data in Excel Without Errors
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Import Multiple Files Using VBA in Excel: A Practical GuideYes. In Excel for Microsoft 365 on Windows, VBA can loop through files in a selected folder, open…
- VBA Dictionary: How It Works and When to Use ItYes. A VBA Dictionary exists as the Scripting.Dictionary object: a key-value container that lets a macro store items…
- Excel VBA: Open Multiple Files in a Folder with a LoopTo open every Excel workbook in a folder, use VBA’s Dir function to find each matching filename, then…
- VBA Scripting.Dictionary: How It Works and When to Use ItA VBA Scripting.Dictionary is an object that stores pairs of unique keys and associated values, letting a macro…
- VBA Range Is Empty? How to Check Cells CorrectlyTo check whether a VBA range contains no entered values or formulas, use Application.CountA(target) = 0. First make…
- Excel UserForm TextBox: How to Wrap TextTo wrap text in an Excel UserForm TextBox, set its MultiLine property to True and its WordWrap property…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.