This page has 0 threads | Add post

The VBA InputBox Command - VBA macros

Written by Andy Brown

In this tutorial

What are Input Boxes?
Syntax of an InputBox function
Possible Problems with Input Boxes
Alternatives to InputBox Functions

What are Input Boxes?

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

Writing an InputBox function - the arguments are listed below

Here's an example:

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:

Ask the user for a number.
Try to convert this to an integer.
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:

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.

Previous
tutorial

Selecting cells and ranges

Previous
part

Variables in Excel Visual Basic macros

Top of page

Next
part

Using MsgBox to Display Messages in VBA Macros

Next
tutorial

Object-oriented programming

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on VBA

Messages, input boxes and variables

The VBA InputBox Command - VBA macros

What are Input Boxes?

Syntax of an InputBox function

Possible Problems with Input Boxes

Alternatives to InputBox Functions

Head office

London

Manchester