How to use VBA’s InputBox function to select a range on the fly in Excel

Often, you’ll want Excel users to specify a range that the app then uses in an automated way. Fortunately, Visual Basic for Applications’ InputBox function supports this task, so you won’t have to work very hard! You’ll learn how to select a range using InputBox in this article.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. Excel Online won’t support the VBA procedure in this article. Download the demonstration .xlsm, .xls, and .cls files to easily access the code.

What is InputBox?

VBA’s InputBox function is one of the most helpful functions you’ll use. It displays a dialog box and waits for the user to enter content and then click a button to continue. This function usually stores a String that contains the input value that you can then manipulate in someway using more code. 

This function has only one required argument, prompt:

InputBox(prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfile, context ], [type])

The prompt command is a String expression that the dialog displays; use this to tell the user what to do; usually, this string will prompt the user to enter some type of content. Table A offers a short explanation of each argument, but we’ll be using only the following three: prompt, title, and type. Table B shows the possible type values; we’ll be using 8 because this value returns a Range object.

Table A

Table B

How to use InputBox in VBA

Now that you know about InputBox, let’s use it in a simple procedure. Specifically, the procedure in Listing A will prompt you to select a range. After a few value tests, the procedure will display the range you select in a message box. If you don’t enter a valid range, the function will display a built-in error message.

Listing A

Sub SelRange()

‘Use InputBox to prompt user for range.

‘Test for cancel and a single-cell selection.

Dim rng As Range

On Error Resume Next

Set rng = Application.InputBox( _

      Title:=”Please select a range”, _

      Prompt:=”Select range”, _

      Type:=8)

On Error GoTo 0

‘Test for cancel.

If rng Is Nothing Then Exit Sub

‘Test for single-cell selection. 

‘Remove comment character if single-cell selection is okay.

If rng.Rows.Count = 1 Then

    MsgBox “You’ve selected only one cell.” _

    & “Please select multiple contiguous cells.”, vbOKOnly

    Exit Sub

End If

‘Remove comment to select input range.

‘rng.Select

MsgBox rng.Address

End Sub

If you’re using a ribbon version, be sure to save the workbook as a macro-enabled file or the procedure won’t run. If you’re using a menu version, you can skip this step.

To enter the procedure, press Alt + F11 to open the Visual Basic Editor. In the Project Explorer to the left, select ThisWorkbook so you can run the procedure in any sheet. You can enter the code manually or import the downloadable .cls file. In addition, the macro is in the downloadable .xlsm and .xls files. If you enter the code manually, don’t paste from this web page. Instead, copy the code into a text editor and then paste that code into the ThisWorkbook module. Doing so will remove any phantom web characters that might otherwise cause errors.

SEE: Windows 10: Lists of vocal commands for speech recognition and dictation (free PDF) (TechRepublic)

Now it’s time to use the procedure to prompt you to select a range. For our purposes, the range you select doesn’t matter. To display the InputBox, do the following:

1. Click the Developers tab.
2. Click Macros in the Code group.
3. In the resulting dialog, choose SelRange() as shown in Figure A and click Run.
4. When prompted, select any range (see Figure B), and then click OK.

Figure A

Figure B

As you can see in Figure C, the procedure displays the selected range as text. Click OK to close the message box. Notice that the reference is absolute. If you need something to be relative, add code that removes the appropriate (or all) the $ characters. You can enter the range from the keyboard with or without the $ characters; however, InputBox will convert the reference to absolute.

Figure C

Here’s how the InputBox works in a spreadsheet

After defining the variable rng as a Range object, the Set statement runs InputBox. Because the type argument is 8, the InputBox expects a selection. If you enter anything else, the procedure will display the error message shown in Figure D. That means you don’t need any special error-handling, the statement takes care of it for you.

Figure D

This message is probably specific enough that you won’t want to usurp it. However, you can by capturing the error and adding error handling specific to that error. 

The variable rng stores the selected range as a Range object; when applying this to your own work, you must deal with the variable as a Range object. However, thanks to its many properties and events, it’s unlikely that you’ll have to look beyond them.

This procedure offers no error handling other than the built-in error you see if you enter anything other than a range (Figure D). You’ll want to add context-sensitive handling when you use InputBox in your own workbooks. 

It’s unlikely that you’ll want to work through all those steps every time you want to run the procedure. Instead, add the macro to the Quick Access Toolbar. To do so, read How to add Office macros to the QAT toolbar for quick access.

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see