Automating the enactment process isn't hard if you trust connected VBA's InputBox successful Microsoft Excel.
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.
Data Type Returned
Text the dialog displays. The maximum fig of characters is 1,024, but I urge that you beryllium arsenic succinct arsenic possible.
This optional String look is displayed successful the dialog's rubric bar. If you omit this argument, VBA volition show the exertion name.
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.
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.
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.
This optional String identifies the Help record to usage to supply context-sensitive help. If you usage helpfile, you indispensable besides usage context.
This numeric look is the discourse fig successful helpfile.
This numeric look specifies the instrumentality information type. See Table B for a implicit database of values.
Text (a String)
A logical worth (True oregon False)
A compartment reference, arsenic a Range object
An mistake value, specified arsenic #N/A
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.
'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", _
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
'Remove remark to prime input range.
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.
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.
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.
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.
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.
Microsoft Weekly Newsletter
Be your company's Microsoft insider by speechmaking these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and WednesdaysSign up today
- How to marque less mistakes and enactment much efficiently utilizing predictive substance successful Microsoft 365 (TechRepublic)
- How to usage the galore substance wrapping options successful Microsoft Word (TechRepublic)
- Microsoft 365: A cheat sheet (TechRepublic)
- Zoom vs. Microsoft Teams, Google Meet, Cisco WebEx and Skype: Choosing the close video-conferencing apps for you (free PDF) (TechRepublic)
- Checklist: Securing Windows 10 systems (TechRepublic Premium)
- Everything you request to cognize astir the Microsoft Exchange Server hack (ZDNet)
- Must-read coverage: Windows 10 (TechRepublic connected Flipboard)