This blog explains the lesser known but better Excel VBA InputBox function
There are two ways you can display an input box using Excel macros: the standard VBA InputBox function, or the much better (but lesser known, at least by this blog's author) Application.InputBox function.

Posted by Andy Brown on 17 December 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The two types of InputBox command in Excel VBA

A mea culpa confession of a VBA feature which has been missing from my life for far too long!

What I used to know

So all these years (and there have been a few), I've been teaching that you can ask your user for information like this:

Sub ThinkOfANumber()

'variable to hold a number

Dim MyNumber As Integer

'display dialog box asking user for number

MyNumber = InputBox("Enter number", _

"Number", "Type your number here")

'display the result

MsgBox "You thought of " & MyNumber

End Sub

And so you can!  The above code would display an input box looking like this:

Standard input box

You can see that the title, prompt and default value for the input box have been set.

My new friend - Application.InputBox

So what's the point of this blog?  Well, it turns out that Excel has not one, but two VBA InputBox functions:

Function Notes
InputBox A generic VBA function to get information from a user.
Application.InputBox An Excel-specific souped-up version of the same function.

You can see the difference when you type them in.  First the standard InputBox:

InputBox arguments

The standard input box arguments.

Compare that with the Excel version:

Application.InputBox arguments

The biggest difference is that the Excel-specific input box has an extra Type argument.

Using Application.InputBox

Here's an example of the same code as above, but with the Type argument set to 1:

Sub ThinkOfANumber()

'variable to hold a number

Dim MyNumber As Integer

'display dialog box asking user for number

MyNumber = Application.InputBox( _

prompt:="Type in a number", _

Title:="Number box", _

Default:="Type your number here", _

Type:=1)

'display the result

MsgBox "You thought of " & MyNumber

End Sub

Suppose that you now run this macro, but don't type in a number:

Not a number

This definitely isn't a number!

 

The Excel version of Application.InputBox will immediately detect this, and show an error:

Number is not valid

You won't be able to proceed until you either type in a valid number or press Cancel.

 

Possible values for the Type argument

Here are the possible values that you can put for the Type argument:

Type argument What you must enter
0 A formula
1 A number
2 A string of text
4 A logical value (True or False)
8 A cell reference (see separate heading below)
16 An error value, such as #N/A
64 An array of values

The most useful arguments are likely to be 1 (to force a user to input a number) or 8 (for a range).

Range input using Application.InputBox

Setting the Type argument to 8 allows a user to choose a range of cells.  Here's some sample code illustrating this (note that this example assumes that you know about object variables and error trapping):

Sub GetRange()

'this will hold reference to range chosen

Dim ChosenRange As Range

'ask user for a range

On Error GoTo NothingChosen

Set ChosenRange = Application.InputBox( _

prompt:="Choose a range", _

Type:=8)

'go to range chosen by user

ChosenRange.Select

Exit Sub

NothingChosen:

MsgBox "You didn't choose anything!"

End Sub

If you run this code, you'll see a dialog box like this:

Input range dialog box

You can now select a range of cells.

 

You can now either type in a range or select it:

Selecting a range

Here we've gone for 6 cells at the top left of a worksheet.

 

When you choose OK, Excel will check that this is a range, and if it is the rest of the procedure will select it.  If you click on the Cancel button, on the other hand, the error trap will kick in and you'll see this message:

Error trap

If you cancel out of the dialog box, the range variable won't be set and you'll get an error.

 

 

And there endeth my confession!  For those wanting a fuller picture, there's much more about Application.InputBox in my colleague Andrew's tutorial video on the subject.

This blog has 0 threads Add post