In VBA, the standard InputBox function returns a zero-length string when the user clicks Cancel. It also returns a zero-length string if the user submits an empty answer, so the function alone cannot tell those two actions apart; in Excel VBA, use Application.InputBox when you need to identify Cancel separately.
What the standard VBA InputBox returns
The VBA InputBox function displays a simple dialog with a prompt, a text field, and OK and Cancel buttons. Its return value is a String. Clicking OK returns the text the person entered, while clicking Cancel returns an empty string, written as "" or represented by VBA’s vbNullString constant.
The important limitation is that submitting the dialog with the text field empty also produces an empty string. In both cases, the calling code receives the same value. This is not an error condition: Cancel does not automatically raise a VBA error, and checking Err.Number will not reveal whether the button was clicked.
If an empty answer and Cancel should have the same outcome, checking for an empty string is enough. If they mean different things in your workflow, use Excel’s Application.InputBox or a custom form instead.
Handle an empty result from InputBox
Here is a basic pattern for treating both Cancel and an empty submission as a reason to stop. The sample then displays any nonempty text. It does not edit workbook data, but save a copy of the workbook before running a macro that you adapt to change cells or other content; VBA macros cannot be undone with Ctrl+Z.
Sub ReadTextWithInputBox()
Dim response As String
' Ask for text; Cancel and an empty submission both return an empty string.
response = VBA.InputBox(Prompt:="Enter a project name:", _
Title:="Project name")
If response = vbNullString Then
MsgBox "No project name was entered.", vbInformation
Exit Sub
End If
MsgBox "You entered: " & response, vbInformation
End Sub
The If response = vbNullString Then test is a practical guard, but its message should not claim that the user definitely clicked Cancel. They might have clicked OK without entering anything. If your prompt requires a value, treating both outcomes as “no value” is often the clearest behavior.
Distinguish Cancel with Excel Application.InputBox
In Excel VBA, Application.InputBox can return a Boolean value of False when the user clicks Cancel. Its result should be stored in a Variant, because the returned value can have different types depending on the Type argument. For text entry, set Type:=2.
Check the result’s type before checking whether it is False. That distinction matters because a person could type the word False; with a text input, that answer is a String, not the Boolean value used for Cancel.
Sub ReadTextAndDetectCancel()
Dim answer As Variant
' Save a copy before running any adapted macro that changes workbook content.
answer = Application.InputBox(Prompt:="Enter a project name:", _
Title:="Project name", _
Type:=2)
' Cancel returns Boolean False; typed text is returned as a String.
If VarType(answer) = vbBoolean Then
If answer = False Then
MsgBox "You cancelled the prompt.", vbInformation
Exit Sub
End If
End If
If Len(CStr(answer)) = 0 Then
MsgBox "The answer is blank.", vbExclamation
Exit Sub
End If
MsgBox "You entered: " & CStr(answer), vbInformation
End Sub
The nested checks are intentional. VBA does not short-circuit the And operator, so combining a type test and a value comparison in one expression can still evaluate both sides. Separate checks make the expected Boolean-versus-text handling explicit. The sample also treats a blank answer as a separate case after it has ruled out Cancel.
Choosing between the two input methods
Use the standard function when you only need a text value and empty input can be handled the same as Cancel. Use Excel’s method when Cancel needs a distinct response, or when Excel should validate a particular kind of input. The choice is about the behavior you need, not just syntax.
- VBA
InputBox: Returns a String and is available in VBA beyond Excel. Its Cancel and empty-submission results are indistinguishable. - Excel
Application.InputBox: Returns a Variant and signals Cancel with Boolean False. ItsTypeargument can request text, a number, a logical value, a range, or other supported input types. - A UserForm: Takes more setup, but gives you control over buttons, validation, layout, and separate outcomes for OK and Cancel.
Application.InputBox belongs to Excel’s Application object. It is not a general replacement in every Office host, such as Word or Outlook. If code needs to run in several Office applications, check the host application’s object model and consider using the standard VBA function or a UserForm.
Use the Type argument for validation
Excel’s Application.InputBox can limit what kind of answer Excel accepts. For example, Type:=1 requests a number, Type:=2 requests text, and Type:=8 requests a range reference. A range result should be handled as a Range object, while a text result is usually handled as a String inside a Variant. Choose the type to match the task rather than accepting arbitrary text and hoping it can be converted later.
For instance, if you request a number, still consider what your macro should do when the person cancels, enters a value outside the useful range, or supplies a number that is valid but inappropriate for the operation. Type validation checks the kind of input; it does not decide your business rules. A prompt requesting a percentage may accept a numeric answer of 500, even if your procedure should only allow values from 0 to 100.
When using Type:=8, assign the returned value to an object variable and use Set when assigning it. That result is a selected range rather than a text address. A type mismatch between the requested input and the variable or later code is a common source of confusing VBA errors.
Common mistakes and troubleshooting
- Assuming an empty string proves Cancel: It does not with the standard function. Use wording such as “No answer was supplied” unless your program genuinely knows which action occurred.
- Comparing a Variant to False without checking its type: With
Application.InputBox, testVarTypefirst. A user-entered text value can resemble a Boolean when read casually, but is still a different type. - Using
On Errorto detect Cancel: Cancel is represented by the return value; it is not normally an exception. Error handling is not a substitute for checking the result. - Converting a result too soon: If you turn a Variant into a number or another type before checking for Cancel, you can lose the distinction or trigger a conversion error. Inspect the return value first.
- Forgetting the host application: The Excel-specific
Application.InputBoxexamples depend on Excel. A macro written for another Office app should not assume that the same Excel method is available.
Also plan for what happens after the prompt. A safe macro should exit cleanly when there is no usable answer and should avoid partially applying changes before it has validated all required input. If the procedure changes workbook data, save a separate copy first: running a VBA macro cannot be reversed with Excel’s Undo command.
When a UserForm is a better alternative
A UserForm is useful when a simple dialog cannot express the choices clearly. You can provide explicit buttons such as Save, Skip, and Cancel, keep OK disabled until required fields are filled, show instructions beside a field, or collect multiple related values at once. You can also make the form’s close button follow a defined cancellation path.
That control comes with extra work: you create the form, add controls, write event procedures, and decide how the calling macro receives the result. For a single optional text value, this is usually unnecessary. For a repeated data-entry workflow or a process where blank and Cancel must have unmistakably different meanings, the extra structure can prevent user confusion.
A regular message box is not a direct substitute when you need typed text: it offers predefined buttons rather than a text field. It can, however, be useful after an input prompt to confirm an action or explain why a value was rejected.
Frequently Asked Questions
What does InputBox return when I click Cancel in VBA?
The standard VBA InputBox returns an empty string. Excel’s Application.InputBox returns Boolean False when Cancel is clicked, so store its result in a Variant and check the type.
How can I tell Cancel from a blank answer?
You cannot distinguish them with the standard InputBox return value: both are empty strings. In Excel VBA, use Application.InputBox and test for a Boolean False result before handling a blank text answer.
Does clicking Cancel cause a VBA error?
No. Cancel is a normal result from the prompt, not an error that you should expect to catch with On Error. Test the returned value instead.
Can I use Application.InputBox outside Excel?Application.InputBox as described here is an Excel-specific method. For VBA in another Office program, use that application’s supported object model, the standard VBA function, or a UserForm as appropriate.
Can Application.InputBox ask the user to select cells?
Yes. In Excel, use Type:=8 to request a range selection. Handle the returned range as an object, and check for cancellation before working with the selection.
Related
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Find a VBA Project Password in Excel SafelyExcel cannot display or reveal a forgotten VBA project password. To regain legitimate access, check your password manager…
- VBA Function As Boolean: How to Return True or FalseYes. In VBA, declare a function with As Boolean when it should return either True or False, then…
- Py Macro Recorder: How Python Records and Replays ActionsA Py macro recorder is a tool for capturing keyboard or mouse actions and replaying them, often with…
- Excel VBA TextBox: Add, Configure, and Use It in FormsAn Excel VBA TextBox is a text-entry control that lets a workbook user type information, such as a…
- Excel UserForm: How to Create, Design, and Use FormsTo create an Excel UserForm in Microsoft 365 for Windows, press Alt+F11 to open the Visual Basic Editor,…
- Excel VBA Active Worksheet: How It Works and When to Use ItIn Excel VBA, ActiveSheet refers to the sheet currently selected in the active workbook. It is not a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.