This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

What I used to know
My new friend - Application.InputBox
Using Application.InputBox
Possible values for the Type argument
Range input using Application.InputBox

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:

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:

The standard input box arguments.

Compare that with the Excel version:

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:

This definitely isn't a number!

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

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:

You can now select a range of cells.

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

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:

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, or why not meet him (or another owl) in person and attend our Introduction to VBA course.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

The two types of InputBox command in Excel VBA

What I used to know

My new friend - Application.InputBox

Using Application.InputBox

Possible values for the Type argument

Range input using Application.InputBox

Head office

London

Manchester