Your Ultimate Information Platform

Easy methods to use VBA’s InputBox perform to pick a spread on the fly in Excel



Automating the choice course of is not tough when you depend on VBA’s InputBox in Microsoft Excel.

Black woman looking at spreadsheets on screens

Picture: Andrey_Popov/Shutterstock

Typically, you may need Excel customers to specify a spread that the app then makes use of in an automatic manner. Happily, Visible Primary for Functions’ InputBox perform helps this job, so you will not should work very laborious! You may discover ways to choose a spread utilizing InputBox on this article.

SEE: 83 Excel suggestions each consumer ought to grasp (TechRepublic)

I am utilizing Microsoft 365 on a Home windows 10 64-bit system, however you need to use earlier variations. Excel On-line will not assist the VBA process on this article. Obtain the demonstration .xlsm, .xls, and .cls recordsdata to simply entry the code.

What’s InputBox?

VBA’s InputBox perform is likely one of the most useful features you may use. It shows a dialog field and waits for the consumer to enter content material after which click on a button to proceed. This perform often shops a String that accommodates the enter worth which you can then manipulate in someway utilizing extra code. 

This perform has just one required argument, immediate:

InputBox(immediate, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfilecontext ], [type])

The immediate command is a String expression that the dialog shows; use this to inform the consumer what to do; often, this string will immediate the consumer to enter some kind of content material. Desk A provides a brief clarification of every argument, however we’ll be utilizing solely the next three: immediate, title, and kind. Desk B exhibits the doable kind values; we’ll be utilizing 8 as a result of this worth returns a Vary object.

Desk A



Knowledge Kind Returned


Textual content the dialog shows. The utmost variety of characters is 1,024, however I like to recommend that you simply be as succinct as doable.



This elective String expression is displayed within the dialog’s title bar. In the event you omit this argument, VBA will show the applying identify.



This elective String expression is displayed within the dialog’s textual content field as a default worth that the consumer can settle for somewhat than getting into new content material. In the event you omit, the textual content field is empty.



This elective numeric expression specifies, in twips, the horizontal distance of the left fringe of the dialog field from the left fringe of the display. If omitted, the dialog field is horizontally centered.



This elective numeric expression specifies, in twips, the vertical distance of the higher fringe of the dialog field from the highest of the display. If omitted, the dialog field is vertically centered. Use xpos and ypos collectively to find out the place the dialog field is displayed on display.



This elective String identifies the Assist file to make use of to supply context-sensitive assist. If you utilize helpfile, you will need to additionally use context.



This numeric expression is the context quantity in helpfile.




This numeric expression specifies the return knowledge kind. See Desk B for a whole record of values. 


Desk B




A components


A quantity


Textual content (a String)


A logical worth (True or False)


A cell reference, as a Vary object


An error worth, corresponding to #N/A


An array of values

Easy methods to use InputBox in VBA

Now that you recognize about InputBox, let’s use it in a easy process. Particularly, the process in Itemizing A will immediate you to pick a spread. After just a few worth checks, the process will show the vary you choose in a message field. In the event you do not enter a legitimate vary, the perform will show a built-in error message.

Itemizing A

Sub SelRange()

‘Use InputBox to immediate consumer for vary.

‘Take a look at for cancel and a single-cell choice.

Dim rng As Vary

On Error Resume Subsequent

Set rng = Software.InputBox( _

      Title:=”Please choose a spread”, _

      Immediate:=”Choose vary”, _


On Error GoTo 0

‘Take a look at for cancel.

If rng Is Nothing Then Exit Sub

‘Take a look at for single-cell choice. 

‘Take away remark character if single-cell choice is okay.

If rng.Rows.Depend = 1 Then

    MsgBox “You have chosen just one cell.” _

    & “Please choose a number of contiguous cells.”, vbOKOnly

    Exit Sub

Finish If

‘Take away remark to pick enter vary.


MsgBox rng.Tackle

Finish Sub

In the event you’re utilizing a ribbon model, remember to save the workbook as a macro-enabled file or the process will not run. In the event you’re utilizing a menu model, you possibly can skip this step.

To enter the process, press Alt + F11 to open the Visible Primary Editor. Within the Undertaking Explorer to the left, choose ThisWorkbook so you possibly can run the process in any sheet. You’ll be able to enter the code manually or import the downloadable .cls file. As well as, the macro is within the downloadable .xlsm and .xls recordsdata. In the event you enter the code manually, do not paste from this internet web page. As a substitute, copy the code right into a textual content editor after which paste that code into the ThisWorkbook module. Doing so will take away any phantom internet characters that may in any other case trigger errors.

SEE: Home windows 10: Lists of vocal instructions for speech recognition and dictation (free PDF) (TechRepublic)

Now it is time to use the process to immediate you to pick a spread. For our functions, the vary you choose would not matter. To show the InputBox, do the next:

  1. Click on the Builders tab.
  2. Click on Macros within the Code group.
  3. Within the ensuing dialog, select SelRange() as proven in Determine A and click on Run.
  4. When prompted, choose any vary (see Determine B), after which click on OK.

Determine A


Determine B


Use the mouse to pick a spread. 

As you possibly can see in Determine C, the process shows the chosen vary as textual content. Click on OK to shut the message field. Discover that the reference is absolute. In the event you want one thing to be relative, add code that removes the suitable (or all) the $ characters. You’ll be able to enter the vary from the keyboard with or with out the $ characters; nevertheless, InputBox will convert the reference to absolute.

Determine C


  The process shows the chosen vary as textual content.

This is how the InputBox works in a spreadsheet

After defining the variable rng as a Vary object, the Set assertion runs InputBox. As a result of the kind argument is 8, the InputBox expects a range. In the event you enter the rest, the process will show the error message proven in Determine D. Which means you do not want any particular error-handling, the assertion takes care of it for you.

Determine D


  InputBox will inform you once you’ve entered something however a spread choice. 

This message might be particular sufficient that you simply will not need to usurp it. Nevertheless, you possibly can by capturing the error and including error dealing with particular to that error. 

The variable rng shops the chosen vary as a Vary object; when making use of this to your individual work, you will need to cope with the variable as a Vary object. Nevertheless, due to its many properties and occasions, it is unlikely that you will have to look past them.

This process provides no error dealing with aside from the built-in error you see when you enter something aside from a spread (Determine D). You may need to add context-sensitive dealing with once you use InputBox in your individual workbooks. 

It is unlikely that you will need to work by means of all these steps each time you need to run the process. As a substitute, add the macro to the Fast Entry Toolbar. To take action, learn Easy methods to add Workplace macros to the QAT toolbar for fast entry.

Additionally see


Leave A Reply

Your email address will not be published.