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

3 years ago 442

Automating the enactment process isn't hard if you trust connected VBA's InputBox successful Microsoft Excel.

Black pistillate looking astatine spreadsheets connected screens

Often, you'll privation Excel users to specify a scope that the app past uses successful an automated way. Fortunately, Visual Basic for Applications' InputBox relation supports this task, truthful you won't person to enactment precise hard! You'll larn however to prime a scope utilizing InputBox successful this article.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system, but you tin usage earlier versions. Excel Online won't enactment the VBA process successful this article. Download the objection .xlsm, .xls, and .cls files to easy entree the code.

What is InputBox?

VBA's InputBox relation is 1 of the astir adjuvant functions you'll use. It displays a dialog container and waits for the idiosyncratic to participate contented and past click a fastener to continue. This relation usually stores a String that contains the input worth that you tin past manipulate successful someway utilizing much code.

This relation has lone 1 required argument, prompt:

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

The punctual bid is simply a String look that the dialog displays; usage this to archer the idiosyncratic what to do; usually, this drawstring volition punctual the idiosyncratic to participate immoderate benignant of content. Table A offers a abbreviated mentation of each argument, but we'll beryllium utilizing lone the pursuing three: prompt, title, and type. Table B shows the imaginable benignant values; we'll beryllium utilizing 8 due to the fact that this worth returns a Range object.

Table A

Argument	Explanation	Data Type Returned
Prompt	Text the dialog displays. The maximum fig of characters is 1,024, but I urge that you beryllium arsenic succinct arsenic possible.	String
Title	This optional String look is displayed successful the dialog's rubric bar. If you omit this argument, VBA volition show the exertion name.	Variant
Default	This optional String look is displayed successful the dialog's substance container arsenic a default worth that the idiosyncratic tin judge alternatively than entering caller content. If you omit, the substance container is empty.	Variant
Xpos	This optional numeric look specifies, successful twips, the horizontal region of the near borderline of the dialog container from the near borderline of the screen. If omitted, the dialog container is horizontally centered.	Variant
Ypos	This optional numeric look specifies, successful twips, the vertical region of the precocious borderline of the dialog container from the apical of the screen. If omitted, the dialog container is vertically centered. Use xpos and ypos together to find wherever the dialog container is displayed connected screen.	Variant
Helpfile	This optional String identifies the Help record to usage to supply context-sensitive help. If you usage helpfile, you indispensable besides usage context.	Variant
Context	This numeric look is the discourse fig successful helpfile.	Variant
Type	This numeric look specifies the instrumentality information type. See Table B for a implicit database of values.	Variant

Table B

Value	Explanation
	A formula
1	A number
2	Text (a String)
4	A logical worth (True oregon False)
8	A compartment reference, arsenic a Range object
16	An mistake value, specified arsenic #N/A
64	An array of values

How to usage InputBox successful VBA

Now that you cognize astir InputBox, let's usage it successful a elemental procedure. Specifically, the process successful Listing A volition punctual you to prime a range. After a fewer worth tests, the process volition show the scope you prime successful a connection box. If you don't participate a valid range, the relation volition show a built-in mistake message.

Listing A

Sub SelRange()

'Use InputBox to punctual idiosyncratic for range.

'Test for cancel and a single-cell selection.

Dim rng As Range

On Error Resume Next

Set rng = Application.InputBox( _

Title:="Please prime 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 remark quality if single-cell enactment is okay.

If rng.Rows.Count = 1 Then

MsgBox "You've selected lone 1 cell." _

& "Please prime aggregate contiguous cells.", vbOKOnly

Exit Sub

End If

'Remove remark to prime input range.

'rng.Select

MsgBox rng.Address

End Sub

If you're utilizing a ribbon version, beryllium definite to prevention the workbook arsenic a macro-enabled record oregon the process won't run. If you're utilizing a paper version, you tin skip this step.

To participate the procedure, property Alt + F11 to unfastened the Visual Basic Editor. In the Project Explorer to the left, prime ThisWorkbook truthful you tin tally the process successful immoderate sheet. You tin participate the codification manually oregon import the downloadable .cls file. In addition, the macro is successful the downloadable .xlsm and .xls files. If you participate the codification manually, don't paste from this web page. Instead, transcript the codification into a substance exertion and past paste that codification into the ThisWorkbook module. Doing truthful volition region immoderate phantom web characters that mightiness different origin errors.

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

Now it's clip to usage the process to punctual you to prime a range. For our purposes, the scope you prime doesn't matter. To show the InputBox, bash the following:

Click the Developers tab.
Click Macros successful the Code group.
In the resulting dialog, take SelRange() arsenic shown successful Figure A and click Run.
When prompted, prime immoderate scope (see Figure B), and past click OK.

Figure A

Figure B

As you tin spot successful Figure C, the process displays the selected scope arsenic text. Click OK to adjacent the connection box. Notice that the notation is absolute. If you request thing to beryllium relative, adhd codification that removes the due (or all) the $ characters. You tin participate the scope from the keyboard with oregon without the $ characters; however, InputBox volition person the notation to absolute.

Figure C

Here's however the InputBox works successful a spreadsheet

After defining the adaptable rng arsenic a Range object, the Set connection runs InputBox. Because the benignant statement is 8, the InputBox expects a selection. If you participate thing else, the process volition show the mistake connection shown successful Figure D. That means you don't request immoderate peculiar error-handling, the connection takes attraction of it for you.

Figure D

This connection is astir apt circumstantial capable that you won't privation to usurp it. However, you tin by capturing the mistake and adding mistake handling circumstantial to that error.

The adaptable rng stores the selected scope arsenic a Range object; erstwhile applying this to your ain work, you indispensable woody with the adaptable arsenic a Range object. However, acknowledgment to its galore properties and events, it's improbable that you'll person to look beyond them.

This process offers nary mistake handling different than the built-in mistake you spot if you participate thing different than a scope (Figure D). You'll privation to adhd context-sensitive handling erstwhile you usage InputBox successful your ain workbooks.

It's improbable that you'll privation to enactment done each those steps each clip you privation to tally the procedure. Instead, adhd the macro to the Quick Access Toolbar. To bash so, read How to adhd Office macros to the QAT toolbar for speedy access.