Excel VBA InputBox Range Selection: How to Use Type 8

VBA & macros · Updated November 21, 2026

To let someone select cells in Excel VBA, use Application.InputBox with Type:=8; it returns the selection as a Range object. Assign that result with Set, and handle Cancel because it does not return a usable range.

What range selection means in VBA

Excel has two similarly named input boxes. The VBA InputBox function asks for text and returns a string. It is useful for questions such as “What name should I use?” but it does not offer a built-in mode for selecting worksheet cells. For range selection, use Excel’s Application.InputBox method instead.

The method’s Type argument controls what the user may enter. Setting it to 8 makes the prompt accept a cell reference or a range selected on a worksheet. After the dialog appears, the user can point to cells with the mouse, enter a reference, and confirm. Your macro receives a Range object, so it can read or work with the selected cells without parsing an address string.

This is useful when a macro should operate on a range chosen at run time rather than on a fixed address such as A2:A20. It also lets users select a range on a different sheet from the one that was active when the macro started. Your code should still check that the selection is appropriate for the task.

Use Application.InputBox with Type 8

A minimal example declares a Range variable, displays the selection prompt, and reports the selected address. The narrow error-handling block allows the macro to continue if the user cancels instead of choosing a range.

Sub ChooseRange()
    Dim chosen As Range

    ' Ask the user to select cells.
    On Error Resume Next
    Set chosen = Application.InputBox( _
        Prompt:="Select a range, then click OK.", _
        Title:="Choose cells", _
        Type:=8)
    On Error GoTo 0

    If chosen Is Nothing Then
        MsgBox "No range was selected.", vbInformation
        Exit Sub
    End If

    MsgBox "Selected range: " & chosen.Address(External:=True), vbInformation
End Sub

The important details are Application.InputBox, Type:=8, and Set. Since the result is an object, omitting Set is not the right way to assign it to a Range variable. Address(External:=True) displays a workbook- and sheet-qualified reference, which helps distinguish selections on different sheets.

A safe complete pattern

The example above does not change worksheet data, but the same selection pattern can be the first step in a larger macro. Before running any macro that will edit, clear, or format cells, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z. Review the selected range before applying an operation, especially if a user could choose an entire column or a large block by mistake.

Keep error handling limited to the call that opens the dialog. On Error Resume Next tells VBA to continue after an error, so leaving it active across the rest of a procedure can conceal unrelated problems. The example immediately restores normal error handling with On Error GoTo 0, then checks whether a range was assigned.

Cancellation is the case that most often causes confusion. When a range is expected, Cancel does not provide a Range object for the macro to use. The assignment can raise an error; with the brief error handler in place, the variable remains Nothing. Checking that condition lets the procedure stop cleanly instead of trying to use an absent selection.

Handle Cancel and validate the selection

Cancel handling is only the first check. A user can select a valid range that is unsuitable for the macro. For example, a routine designed to process one contiguous block may not be able to handle several separate areas, and a routine intended for one worksheet may need to reject a selection from another sheet.

After the InputBox returns, inspect the range before acting on it. Common checks include:

  • Worksheet: compare chosen.Worksheet with the worksheet your procedure expects.
  • Contiguous cells: check whether the selection has more than one area if your logic requires one uninterrupted block.
  • Size: check chosen.Cells.CountLarge before processing a potentially huge selection.
  • Shape: verify row or column counts if the task expects a single row, a single column, or a fixed-size block.
  • Contents: decide whether blank cells, formulas, or text are acceptable; a valid range reference does not guarantee suitable cell contents.

Validation rules depend on the job. A macro that totals selected numeric cells may reasonably accept multiple areas, while a macro that fills a rectangular template may require one contiguous block with a precise number of rows and columns. State important restrictions in the prompt, then enforce them in code rather than relying only on the user to follow instructions.

Common problems and their causes

  • Using the wrong InputBox: InputBox is for text. Use Application.InputBox for the typed range-selection mode.
  • Leaving out Type:=8: without the range type, the prompt is not configured to return a Range object.
  • Leaving out Set: a Range variable holds an object reference and must be assigned with Set.
  • Not checking for Cancel: later code may fail when it tries to use a range that was never selected.
  • Assuming a particular sheet: the user can select cells on another worksheet. Qualify references and verify the selected range’s worksheet where necessary.
  • Assuming one rectangular area: a selection can contain multiple areas. Code that expects a simple rectangle should check that assumption before processing.

Another common misconception is that Type 8 returns only the address as text. It returns a Range object, which is why you can use properties such as Address, Rows.Count, and Worksheet on the result. If you need to store an address for later, you can read the object’s address property; that is a separate step from receiving the selected range.

How it compares with other selection methods

Application.InputBox is a good fit when the user should choose a range during a macro run and the interaction can be brief. It is quick to add, does not require building a custom form, and returns a reference that VBA can use directly.

A fixed range in code is simpler when the target never changes, but it is less flexible. The ordinary VBA InputBox can ask someone to type an address, but then your code must interpret that text and handle invalid references. For more involved workflows—such as choosing among several options, showing instructions, or collecting multiple pieces of information—a UserForm can provide a more structured interface. That added flexibility takes more design and maintenance than a one-time range prompt.

Do not confuse selecting a range with choosing a file. Type 8 is for worksheet references; file selection requires a different dialog or method. Likewise, a prompt that accepts a range does not automatically make subsequent operations safe: the macro still needs to decide what actions are allowed on the chosen cells.

Windows, Mac, and Excel for the web

This VBA pattern applies to desktop Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA and the Excel object model, although the Visual Basic Editor and some surrounding interface details differ from Windows. The Application.InputBox range-selection approach is a VBA feature, not a worksheet formula or a browser interface.

Excel for the web does not run VBA macros. A workbook containing VBA can be opened there, but users need desktop Excel to run this macro. If people must perform the same task in a browser, consider an alternative designed for that environment, such as an Office Script or a redesigned workflow; it will not run this VBA code as-is.

On Windows, Alt+F11 opens the Visual Basic Editor in Excel. Keyboard behavior can differ on Mac depending on the keyboard and system settings, so use Excel’s Macro or Tools menus to locate the Visual Basic Editor if that shortcut is unavailable. Regardless of platform, macro security settings may affect whether VBA can run; do not enable macros in an untrusted workbook merely to use a range prompt.

Frequently Asked Questions

How do I select a range with an Excel VBA InputBox?
Call Application.InputBox with Type:=8, assign the result to a Range variable using Set, and check whether the variable is Nothing after cancellation.

What is the difference between InputBox and Application.InputBox?
The VBA InputBox function collects a text response. Excel’s Application.InputBox method has a Type argument, including Type 8 for selecting a range, and can return a Range object.

Does Type 8 return a cell address or a Range object?
It returns a Range object. Use a property such as chosen.Address if you specifically need the reference as text.

How do I detect when the user clicks Cancel?
When assigning the Type 8 result to a Range variable, handle the cancellation error around the call and then test whether the variable is Nothing. Do not continue as though a range was selected.

Can the user select cells on another worksheet?
Yes. The selected Range can belong to another worksheet, so check chosen.Worksheet if your macro must work on a particular sheet.

Can this VBA range prompt run in Excel for the web?
No. Excel for the web does not execute VBA macros. Run the macro in desktop Excel, or create a separate browser-compatible solution.

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.