Using the InputBox Function in VBA Macros
The InputBox function in Visual Basic for Applications allows you to get - as the name suggests - input from the user. This blog shows how to use it!

This blog is part of our Excel macros online tutorial series.  You might also like to have a look at our VBA macros training courses.

Posted by Andy Brown on 03 October 2011 | 1 comment

The VBA InputBox Command

An input box allows you to type in one piece of information in Visual Basic for Applications, using the InputBox function:

The syntax of InputBox

Writing an InputBox function - the arguments are listed below

Here's an example:

Example of Input Box

This input box allows the user to type in the name of a file that they want to open.

 

This blog explains how to create and use input boxes.  Some of the sample code refers to variables and message boxes, so you may prefer to read these blogs first.

If you want to get more than one bit of information at a time, or want to add fancy things to your form like colours and dropdown boxes, you'll need to learn how to create a user form.

Syntax of an InputBox function

Here is the VBA macro code to display the file name above:

Sub GetFileName()

'the file name we want to open

Dim FileName As String

'display an input box asking for file name

FileName = InputBox( _

"Type in the name of the file you want to open", _

"Choose file name", _

"Type your file name here")

'if no file name chosen, say so and stop

If Len(FileName) = 0 Then

MsgBox "No file name chosen"

Exit Sub

End If

'otherwise, open this file

Workbooks.Open FileName

End Sub

This uses positional arguments, but you could equally well have written the code using named arguments:

'display an input box using positional arguments

FileName = InputBox( _

prompt:="Type in the name of the file you want to open", _

Title:="Choose file name", _

Default:="Type your file name here")

The full list of possible arguments is as follows:

Argument Compulsory What it means
Prompt Yes The question which appears in the dialog box
Title No The title of the dialog box
Default No The default text which appears filled in
XPos No The position of the input box from the left corner of the screen, in TWIPs (see hint below)
YPos No The position of the input box from the top edge of the screen, in TWIPs (see hint below)
HelpFile, Context No Controls what happens when a user clicks on the Help button, and not covered here

There are about 567 TWIPs to the centimetre, if you're wondering (but you knew that anyway, didn't you?).  It's pointless setting an X and Y posiiton anyway, because by default input boxes appear exactly where you'd want them to - in the middle of your user's screen.

Possible Problems with Input Boxes

When a user chooses the Cancel button, the InputBox function returns an empty string (ie a string of characters zero characters long!).  You should always check for this - in the code below, we check if the variable FileName has length 0 before proceeding:

'the file name we want to open

Dim FileName As String

'display an input box using positional arguments

FileName = InputBox( _

prompt:="Type in the name of the file you want to open", _

Title:="Choose file name", _

Default:="Type your file name here")

'if no file name chosen, say so and stop

If Len(FileName) = 0 Then

MsgBox "No file name chosen"

Exit Sub

End If

Another possible problem with an input box is that it might return the wrong type of data.  In the example below, we want to find out how old someone is, and check if the answer is an integer before proceeding:

Sub GetAge()

'the age of the person as a string of text

Dim strAge As String

'the age of the person as an integer

Dim intAge As Integer

'get this person's age

strAge = InputBox("How old are you?")

'if there's an error, jump to bottom of routine

On Error GoTo NotValidAge

intAge = CInt(strAge)

MsgBox "You are " & intAge & " years old"

Exit Sub

NotValidAge:

'jump here if couldn't convert input value to integer

MsgBox "You must enter a valid integer!"

End Sub

In this case our algorithm is:

  1. Ask the user for a number.
  2. Try to convert this to an integer.
  3. Use this integer if we're successful, but otherwise display an error message.

Alternatives to InputBox Functions

If you want to ask your user for more than one bit of information at a time, you're probably better off presenting an Excel worksheet:

Excel form

Much better than a series of input boxes!

Apart from the fact that you avoid displaying a series of input boxes, worksheets like the one above allow you to use Excel features such as data validation and protection to control input.

This blog has 1 comment

Comment added on 21 February 2013 at 10:12 GMT
Hi,
How can I input range of cells? I would like to define macro for creating a pivot table from input range of cells which I would enter. At this time I have macro which creates a pivot table from selected cells but I would like to improve it. I imagine that I would run this macro and would be asked to chose cells for a pivot table.

Is it possible to do it with Input box?


Thanks,
Filip
Reply from Andy Brown
In a word, no - an input box only allows you to type in one value at a time. 

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.