In Excel VBA, the built-in InputBox function returns the user’s response as a String. One important catch is that clicking Cancel also returns an empty string, so this function cannot tell you whether the user canceled or clicked OK without entering text; use Application.InputBox when you need to distinguish those actions.
What an Excel VBA InputBox returns
The VBA InputBox function displays a prompt and gives your code the text the user types. You can assign its result directly to a variable declared as String. If the user enters 42, for example, the result is the text “42,” not a numeric value simply because it looks like a number. Convert it to a number separately if your code needs to perform arithmetic.
A simple example is assigning a name to a variable. The code below uses the built-in VBA function explicitly, which can make it clearer that this is not Excel’s separate Application.InputBox method.
Sub AskForName()
Dim answer As String
' Ask for text and store the response.
answer = VBA.InputBox(Prompt:="Enter a name:", _
Title:="Name", _
Default:="")
If Len(answer) = 0 Then
MsgBox "No text was entered, or the dialog was canceled."
Exit Sub
End If
MsgBox "You entered: " & answer
End Sub
Before running a macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z. On Windows, open the Visual Basic Editor with Alt+F11; on a Mac, Option+F11 may work, sometimes with the Fn key, depending on the keyboard. Excel for the web does not run VBA macros, so use the desktop Excel app.
How the built-in function works
The function’s general syntax is InputBox(Prompt, Title, Default, Xpos, Ypos, HelpFile, Context). Only the prompt is essential. The other arguments let you set the dialog title, prefill an initial response, position the dialog, or associate help content with it. Most macros need only the prompt, title, and sometimes a default value.
The prompt tells the person what to enter. A useful prompt describes the expected value, not merely the action. For instance, “Enter the department code (such as FIN or HR)” gives more guidance than “Enter a value.” A default can show an example or a common choice, but remember that a user may accept it unchanged. Your code should still validate the resulting text.
Because the return value is text, even a response that appears numeric remains a string until converted. VBA’s conversion functions, such as CLng for a whole number or CDbl for a decimal number, can convert suitable text, but invalid input can cause a run-time error. Validate the response before conversion, or use a method that constrains what the dialog accepts.
Cancel, blank input, and empty strings
The most common source of confusion is the meaning of an empty result. With VBA’s InputBox, both clicking Cancel and clicking OK while leaving the response blank produce a zero-length string. Therefore, checking Len(answer) = 0 tells you that there is no text, but it does not reveal which button the user clicked.
That distinction matters when an empty response has a valid meaning. A blank department name might mean “clear the existing value,” while Cancel might mean “leave everything unchanged.” The built-in function cannot reliably support both intentions from its return value alone. If the distinction matters, choose Application.InputBox, whose cancellation result is a Boolean False rather than an empty string.
Do not treat a response containing spaces as automatically blank unless that is the behavior you want. Len(" ") is not zero. To regard text made only of leading or trailing spaces as empty, trim a copy of the response and test that. Preserve the original string if the spaces themselves could be meaningful; trimming changes the text you use.
When to use Application.InputBox instead
Application.InputBox is an Excel method with a Type argument. With Type:=2, it requests text and returns the response in a Variant. If the user clicks Cancel, it returns the Boolean value False. That lets the macro tell cancellation apart from an accepted blank response, provided it checks the Variant’s type before treating it as text.
Sub AskForText()
Dim answer As Variant
' Type 2 requests text; Cancel returns Boolean False.
answer = Application.InputBox(Prompt:="Enter a name:", _
Title:="Name", _
Default:="", _
Type:=2)
If VarType(answer) = vbBoolean Then
MsgBox "You canceled the prompt."
Exit Sub
End If
If Len(CStr(answer)) = 0 Then
MsgBox "You accepted the prompt without entering text."
Exit Sub
End If
MsgBox "You entered: " & CStr(answer)
End Sub
The type check is useful because a person could enter the text False. That response is a string, whereas Cancel is a Boolean. Checking VarType(answer) = vbBoolean distinguishes the returned data types instead of relying on a text comparison that could confuse the two.
Choosing an InputBox type
The built-in VBA function is a good fit when the prompt simply needs a string and treating Cancel the same as a blank response is acceptable. Choose Application.InputBox when you need a cancellation signal, want Excel to constrain the kind of response, or need the user to select a range.
For Application.InputBox, the Type argument determines what Excel accepts. Common options include:
Type:=1requests a number.Type:=2requests text.Type:=4requests a logical value, such as TRUE or FALSE.Type:=8requests a cell or range reference.
Some type values can be combined by adding their numbers, but do that only when you have a specific reason to accept multiple kinds of input. In particular, a range response is not ordinary text: handle it as an object and use an appropriate object variable. For a text-only prompt, Type:=2 is the straightforward choice.
Validating and using the returned text
An InputBox gathers a response; it does not guarantee that the response is suitable for the rest of your macro. Decide what counts as valid before you use it. For a required label, reject an empty or whitespace-only response. For a code, check its length or allowed characters. For a number, convert it only after checking that the text represents an acceptable number.
For example, after distinguishing Cancel with Application.InputBox, a text field can be checked for whitespace-only content with Len(Trim$(CStr(answer))) = 0. Trim$ removes spaces at the start and end; it does not remove spaces inside a phrase. If leading or trailing spaces are intentional data, do not trim them. A validation message should explain what needs fixing, and the macro should let the user try again rather than silently substituting an unrelated value.
Keep the types clear in your code. A String variable is suitable for text from VBA’s function. A Variant is appropriate for Application.InputBox because its result can be a string or, on cancellation, a Boolean. Convert the Variant to a string only after checking whether it represents cancellation. This avoids accidental type conversions and makes the macro’s control flow easier to read.
Common problems and misconceptions
- Expecting Cancel to return a special word: VBA’s
InputBoxreturns an empty string for Cancel, not the word “Cancel.” UseApplication.InputBoxwhen you need to distinguish cancellation. - Assuming digits make the result numeric: A text response such as
123is still text when collected with the VBA function. Convert and validate it before calculations. - Using
Setfor a string:Setassigns object references in VBA. Assign a string response with the ordinary equals sign. - Forgetting that blank and spaces differ: An empty string has length zero; a string containing spaces does not. Use trimming only if whitespace should count as blank.
- Expecting the dialog in Excel for the web: VBA macros run in desktop Excel, not in Excel for the web. Open the workbook in the desktop application to run the macro.
A further practical issue is trust. A macro can do more than display a prompt, so enable or run VBA only in workbooks from sources you trust. Input validation helps prevent accidental bad entries, but it does not make an untrusted macro safe.
Alternatives for more guided entry
An InputBox is quick to add, but it offers only a basic prompt and response field. If the user must choose from a known list, a worksheet drop-down created with Data Validation can reduce spelling differences. If the form needs several fields, buttons, or more detailed instructions, a VBA UserForm provides a more structured interface. Those approaches take more setup but can make repeated data entry clearer and easier to validate.
For a one-off question, an InputBox is often enough. For a workflow that many people use, consider whether the prompt explains the expected format, handles Cancel deliberately, and gives useful feedback for invalid input. Choosing between the simple function, Application.InputBox, and a larger form is mainly a question of how much control and guidance the task requires.
Frequently Asked Questions
Does VBA InputBox return a String?
Yes. The built-in VBA InputBox function returns a String containing the response. A numeric-looking entry is still text until your code converts it.
What does InputBox return when I click Cancel?
The built-in VBA function returns a zero-length string, the same result as clicking OK without entering text. Application.InputBox instead returns Boolean False when canceled.
How do I tell Cancel from a blank answer?
Use Application.InputBox with Type:=2 and store the result in a Variant. Check whether VarType is vbBoolean before treating the response as text.
How can I make InputBox return a number?
The built-in function returns text, even when the response contains digits. Validate the text, then convert it with an appropriate VBA conversion function, or use Application.InputBox with Type:=1 to request a number.
Can I use VBA InputBox in Excel for the web?
No. Excel for the web does not run VBA macros. Use the desktop version of Excel to run code that displays a VBA InputBox.
Related
- Excel Pasting: Stop Tab-Delimited Text Splitting on Paste
- Excel Text to Columns Wizard: Split Data Easily
- Excel Text to Columns: Split Data Cleanly and Safely
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Access VBA For Loop: Syntax, Examples, and Common ErrorsIn Access VBA, use a For...Next loop to repeat instructions a set number of times, and use For…
- Excel VBA: Open a Workbook Without Updating LinksTo open an Excel workbook with VBA and skip updating its external workbook links, pass UpdateLinks:=0 to Workbooks.Open.…
- Excel VBA Find Last Row: Reliable Methods and ExamplesIn Excel VBA, find the last row in one column with Cells(Rows.Count, "A").End(xlUp).Row, replacing A with the column…
- Excel VBA Sub or Function Not Defined? Fix It“Sub or Function not defined” is a VBA compile error: Excel cannot resolve the name of a procedure…
- Excel AutoFit Using View Code: Set It Up with VBATo AutoFit columns automatically when you edit a worksheet, right-click that sheet’s tab, choose View Code, and add…
- VBA MsgBox If: Check a User’s Choice with an If StatementIn VBA, use MsgBox inside an If statement to show a prompt and run different code according to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.