Excel Input Box

An Excel Input Box (InputBox) function is different from a VBA Input Box because you can specify what you would like the result of the Input Box to be. If the Type argument is omitted, the Input Box will return at text (String) value.

Structure (syntax):

Variable = Application.InputBox (Prompt, [Title], [Default],
[XPos],[Ypos], [HelpFile], [HelpContextID], [Type])

Prompt	Text on the Input Box
Title	Title bar text (optional)
Default	Default value of the Input Box (optional)
XPos/Ypos	Position of the Input Box. If you leave them blank, the Input Box will appear in the centre of the screen (optional)
HelpFile	Associated help document attachment (optional)
HelpContextID	Unique identifier for the help document - bookmark (optional)
Type	Defines what data type to return (optional)

The following Types may be used:

Value	Meaning
0	A formula
1	A number
2	Text (a string)
4	A logical value (True or False)
8	A cell reference, as a Range object
16	An error value, such as #N/A
64	An array of values

If you wish the Input Box to accept both text and numbers, set the Type argument to 1 + 2.

To call the Excel InputBox and not the standard VBA InputBox, you need to call the Application object keyword which calls this function from the Excel library where it belongs.

Application.InputBox(...

For the following examples, we will declare the variables as Variant.

Example1 - Text Input Box:

Sub Box1()
    Dim x
    x = Application.InputBox("Please enter a number", , , , , , , 1)
    ActiveCell.Value = x
End Sub

Example2 - Formula Input Box:

Sub box4()
    Dim y
    y = Application.InputBox("Please enter a formula", , , , , , , 0)
    ActiveCell.Value = y
End Sub

While a the Excel InputBox function s being displayed, the macro procedure is paused waiting for the user to click a button.

Next Topic: Making Decisions (If...Then...Else...End If)

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keyword: InuptBox, Application and ActiveCell