Automating the selection process isn't difficult if you rely on VBA's InputBox in Microsoft 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.
Data Type Returned
Text the dialog displays. The maximum number of characters is 1,024, but I recommend that you be as succinct as possible.
This optional String expression is displayed in the dialog's title bar. If you omit this argument, VBA will display the application name.
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.
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.
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.
This optional String identifies the Help file to use to provide context-sensitive help. If you use helpfile, you must also use context.
This numeric expression is the context number in helpfile.
This numeric expression specifies the return data type. See Table B for a complete list of values.
Text (a String)
A logical value (True or False)
A cell reference, as a Range object
An error value, such as #N/A
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.
'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", _
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
'Remove comment to select input range.
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.
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.
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.
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.
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 WednesdaysSign up today
- How to make fewer mistakes and work more efficiently using predictive text in Microsoft 365 (TechRepublic)
- How to use the many text wrapping options in Microsoft Word (TechRepublic)
- Microsoft 365: A cheat sheet (TechRepublic)
- Zoom vs. Microsoft Teams, Google Meet, Cisco WebEx and Skype: Choosing the right video-conferencing apps for you (free PDF) (TechRepublic)
- Checklist: Securing Windows 10 systems (TechRepublic Premium)
- Everything you need to know about the Microsoft Exchange Server hack (ZDNet)
- Must-read coverage: Windows 10 (TechRepublic on Flipboard)