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

Image: Andrey_Popov/Shutterstock

Often you want Excel users to specify a range that the app will then use automatically. Luckily, Visual Basic for Applications’ InputBox function supports this task, so you don’t have to try too hard! This article shows you how to use InputBox to select a range.

SEE: 83 Excel Tips Every User Should Know (TechRepublic)

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

What is InputBox?

VBA’s InputBox function is one of the most useful functions you will use. It displays a dialog box and waits for the user to enter content and then clicks a button to continue. This function usually stores a string containing the input value, which you can then manipulate somehow with more code.

This function has only one required argument, prompt:

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

The prompt command is a string expression that is displayed in the dialog box. Use this to tell the user what to do. Typically, this string prompts the user to enter some type of content. Table A provides a brief explanation for each argument, but we’ll only use the following three: prompt, title, and type. Table B shows the possible type values; we use 8 because that value returns a Range object.

Table A

dispute

explanation

Data type returned

prompt

Text that the dialog box displays. The maximum number of characters is 1,024, but I recommend that you be as concise as possible.

line

title

This optional string expression is displayed in the title bar of the dialog. If you omit this argument, VBA displays the application name.

variant

default

This optional string expression is displayed in the dialog box’s text box as a default value that the user can accept instead of typing new content. If you don’t specify anything, the text box is empty.

variant

Xpos

This optional numeric expression specifies the horizontal distance, in twips, of the left edge of the dialog box from the left edge of the screen. If omitted, the dialog box is centered horizontally.

variant

ypos

This optional numeric expression specifies the vertical distance, in twips, of the top of the dialog box from the top of the screen. If omitted, the dialog will be centered vertically. Use xpos and Yes together to determine where the dialog box appears on the screen.

variant

help file

This optional string identifies the help file to use to provide context-sensitive help. if you use help fileyou must also use context.

variant

context

This numeric expression is the context number in help file.

variant

Type

This numeric expression specifies the return data type. See Table B for a complete list of values.

variant

Table B

value

explanation

0

A formula

1

A number

2

text (a character string)

4

A logical value (True or False)

8th

A cell reference as a Range object

16

An error value, e.g. B. #N/A

64

An array of values

How to use InputBox in VBA

Now that you know InputBox, let’s use it in a simple procedure. In particular, the procedure in Listing A prompts you to select an area. After some value tests, the procedure displays the range you selected in a message box. If you don’t enter a valid range, the function displays a built-in error message.

Listing A

SubSelRange()

‘Use InputBox to prompt the user for the range.

‘Test for abort and a single cell selection.

Dim ring As Range

If error, continue next

Set rng = Application.InputBox( _

Title:=”Please select an area”, _

Prompt:=”Select range”, _

Type:=8)

On error GoTo 0

‘Test for abort.

If rng is nothing, then exit Sub

‘Test for single cell selection.

‘Uncomment if single cell selection is ok.

If rng.Rows.Count = 1 then

MsgBox “You only selected one cell.” _

&Amp; “Please select multiple contiguous cells.”, vbOKOnly

exit sub

end if

‘Remove the input range selection comment.

‘rng.Select

MsgBox rng.Address

end sub

If you are using a ribbon version, be sure to save the workbook as a macro-enabled file, otherwise the procedure will not run. If you are using a menu version, you can skip this step.

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

SEE: Windows 10: Lists of Voice Commands for Speech Recognition and Dictation (Free PDF) (TechRepublic)

Now it’s time to use the procedure to ask you to select an area. For our purposes, the area you choose is irrelevant. To display the InputBox, do the following:

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

Figure A

Figure B

As you can see in it Figure C, the procedure displays the selected range as text. Click OK to close the message box. Note that the reference is absolute. If you want something to be relative, add code that removes the appropriate (or all) $ characters. You can enter the range from the keyboard with or without the $ sign; However, InputBox converts the reference to an absolute value.

Figure C

This is how the InputBox works in a table

After defining the variable rng as a Range object, the Set statement executes InputBox. Because the type argument is 8, the InputBox expects a choice. If you enter anything else, the procedure displays the error message shown in Figure D. That means you don’t need any special error handling, the statement does it for you.

Figure D

This message is probably specific enough that you don’t want to usurp it. However, you can capture the error and add error handling specific to that error.

The variable rng stores the selected range as a Range object; If you apply this to your own work, you must treat the variable as a Range object. However, thanks to its many properties and events, you’re unlikely to need to look beyond that.

This technique provides no error handling other than the built-in error you see when you type anything other than a range (Figure D). You should add contextual handling when using InputBox in your own workbooks.

It’s unlikely that you’ll want to go through all of these steps every time you want to run the procedure. Add the macro to the Quick Access Toolbar instead. See How to add Office macros to the QAT toolbar for quick access.

Leave a Comment