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

Picture: Andrey_Popov/Shutterstock

Usually, you may need Excel customers to specify a variety that the app then makes use of in an automatic method. Thankfully, Visible Primary for Purposes’ InputBox operate helps this process, so you will not should work very onerous! You will learn to choose a variety 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 help 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 operate is likely one of the most useful capabilities 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 operate often shops a String that comprises the enter worth which you can then manipulate in someway utilizing extra code. 

This operate 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 presents a brief rationalization of every argument, however we’ll be utilizing solely the next three: immediate, title, and sort. Desk B exhibits the attainable kind values; we’ll be utilizing 8 as a result of this worth returns a Vary object.

Desk A

Argument

Clarification

Knowledge Sort Returned

Immediate

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 attainable.

String

Title

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.

Variant

Default

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

Variant

Xpos

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 screen. If omitted, the dialog field is horizontally centered.

Variant

Ypos

This elective numeric expression specifies, in twips, the vertical distance of the higher fringe of the dialog field from the highest of the display screen. 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 screen.

Variant

Helpfile

This elective String identifies the Assist file to make use of to supply 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

Sort   

 

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

Variant

Desk B

Worth

Clarification

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, equivalent to #N/A

64

An array of values

The way 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 variety. After a couple of worth checks, the process will show the vary you choose in a message field. In the event you do not enter a sound vary, the operate 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 = Utility.InputBox( _

      Title:=”Please choose a variety”, _

      Immediate:=”Choose vary”, _

      Sort:=8)

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.

‘rng.Choose

MsgBox rng.Deal with

Finish Sub

In the event you’re utilizing a ribbon model, make sure you save the workbook as a macro-enabled file or the process will not run. In the event you’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 Challenge 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 recordsdata. In the event you enter the code manually, do not paste from this net 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 net characters which 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 variety. For our functions, the vary you choose does 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. In the event you 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; nevertheless, InputBox will convert the reference to absolute.

Determine C

excelselrange-macro-c.jpg

  The process shows the chosen vary as textual content.

Here 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. 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 once you’ve entered something however a variety choice. 

This message might be particular sufficient that you simply 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 take care of 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 presents no error dealing with aside from the built-in error you see if you happen to enter something aside from a variety (Determine D). You will 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 way of 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 The way to add Workplace macros to the QAT toolbar for fast entry.

Additionally see

Source link