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

Usually, you will need Excel customers to specify a variety that the app then makes use of in an automatic manner. Fortuitously, Visible Primary for Functions’ InputBox perform helps this job, so you will not must work very exhausting! You will discover ways to choose a variety utilizing InputBox on this article.

SEE: 83 Excel suggestions each person ought to grasp (eBioPic)

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

What’s InputBox?

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

This perform has just one required argument, immediate:

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

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

Desk A

Argument

Rationalization

Knowledge Kind Returned

Immediate

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

String

Title

This non-obligatory String expression is displayed within the dialog’s title bar. If you happen to omit this argument, VBA will show the appliance title.

Variant

Default

This non-obligatory String expression is displayed within the dialog’s textual content field as a default worth that the person can settle for fairly than coming into new content material. If you happen to omit, the textual content field is empty.

Variant

Xpos

This non-obligatory 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.

Variant

Ypos

This non-obligatory 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.

Variant

Helpfile

This non-obligatory String identifies the Assist file to make use of to offer context-sensitive assist. If you employ helpfile, it’s essential to additionally use context.

Variant

Context

This numeric expression is the context quantity in helpfile.

Variant

Kind   

 

This numeric expression specifies the return information kind. See Desk B for an entire listing of values. 

Variant

Desk B

Worth

Rationalization

0

A method

1

A quantity

2

Textual content (a String)

4

A logical worth (True or False)

8

A cell reference, as a Vary object

16

An error worth, akin to #N/A

64

An array of values

How one can use InputBox in VBA

Now that about InputBox, let’s use it in a easy process. Particularly, the process in Itemizing A will immediate you to pick a variety. After just a few worth exams, the process will show the vary you choose in a message field. If you happen to do not enter a sound vary, the perform will show a built-in error message.

Itemizing A

Sub SelRange()

‘Use InputBox to immediate person for vary.

‘Check for cancel and a single-cell choice.

Dim rng As Vary

On Error Resume Subsequent

Set rng = Utility.InputBox( _

      Title:=”Please choose a variety”, _

      Immediate:=”Choose vary”, _

      Kind:=8)

On Error GoTo 0

‘Check for cancel.

If rng Is Nothing Then Exit Sub

‘Check 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.

‘rng.Choose

MsgBox rng.Deal with

Finish Sub

If you happen to’re utilizing a ribbon model, you’ll want to save the workbook as a macro-enabled file or the process will not run. If you happen to’re utilizing a menu model, you’ll be able to 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’ll be able to run the process in any sheet. You possibly can enter the code manually or import the downloadable .cls file. As well as, the macro is within the downloadable .xlsm and .xls information. If you happen to enter the code manually, do not paste from this internet web page. As an alternative, 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 which may in any other case trigger errors.

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

Now it is time to use the process to immediate you to pick a variety. 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

excelselrange-macro-a.jpg

Determine B

excelselrange-macro-b.jpg

Use the mouse to pick a variety. 

As you’ll be able to 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. If you happen to want one thing to be relative, add code that removes the suitable (or all) the $ characters. You possibly can enter the vary from the keyboard with or with out the $ characters; nonetheless, InputBox will convert the reference to absolute.

Determine C

excelselrange-macro-c.jpg

  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 sort argument is 8, the InputBox expects a range. If you happen to enter anything, the process will show the error message proven in Determine D. Meaning you do not want any particular error-handling, the assertion takes care of it for you.

Determine D

excelselrange-macro-d.jpg

  InputBox will let you know while you’ve entered something however a variety choice. 

This message might be particular sufficient that you just will not need to usurp it. Nevertheless, you’ll be able to 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, it’s essential 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 gives no error dealing with aside from the built-in error you see when you enter something aside from a variety (Determine D). You will need to add context-sensitive dealing with while you use InputBox in your individual workbooks. 

It is unlikely that you will need to work via all these steps each time you need to run the process. As an alternative, add the macro to the Fast Entry Toolbar. To take action, learn How one can add Workplace macros to the QAT toolbar for fast entry.

Additionally see

Leave a Reply