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

3 years ago 359

Automating the selection process isn't difficult if you rely on VBA's InputBox in Microsoft Excel.

Black woman looking at spreadsheets on screens

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

Argument	Explanation	Data Type Returned
Prompt	Text the dialog displays. The maximum number of characters is 1,024, but I recommend that you be as succinct as possible.	String
Title	This optional String expression is displayed in the dialog's title bar. If you omit this argument, VBA will display the application name.	Variant
Default	This optional String expression is displayed in the dialog's text box as a default value that the user can accept rather than entering new content. If you omit, the text box is empty.	Variant
Xpos	This optional numeric expression specifies, in twips, the horizontal distance of the left edge of the dialog box from the left edge of the screen. If omitted, the dialog box is horizontally centered.	Variant
Ypos	This optional numeric expression specifies, in twips, the vertical distance of the upper edge of the dialog box from the top of the screen. If omitted, the dialog box is vertically centered. Use xpos and ypos together to determine where the dialog box is displayed on screen.	Variant
Helpfile	This optional String identifies the Help file to use to provide context-sensitive help. If you use helpfile, you must also use context.	Variant
Context	This numeric expression is the context number in helpfile.	Variant
Type	This numeric expression specifies the return data type. See Table B for a complete list of values.	Variant

Table B

Value	Explanation
	A formula
1	A number
2	Text (a String)
4	A logical value (True or False)
8	A cell reference, as a Range object
16	An error value, such as #N/A
64	An array of values

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:

Click the Developers tab.
Click Macros in the Code group.
In the resulting dialog, choose SelRange() as shown in Figure A and click Run.
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.