VBA InputBox Example: Get and Validate User Input

VBA & macros · Updated November 21, 2026

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 InputBox returns 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.