VBA Input Box

An Input Box (InputBox) is a function that allows the user to enter a value into a dialog box. The result of an Input Box is stored normally to a variable and remebered for later use in a procedure. Note that the result of an Input Box is always returns a String value.

Structure (syntax):

Variable = InputBox (Prompt, [Title], [Default], [XPos], [YPos])

The Arguments for an InputBox:

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)

Example1 - Text Input Box:

Sub Box1()
    Dim strMyName As String
    strMyName = InputBox("Please enter your name", "Data Entry")
    ActiveCell.Value = "My name is " & strMyName
End Sub

If you click on the Cancel button, it will return an empty string " " so the result will be "My name is"

Example2 - Using named arguments:

This allows you to put the arguments in any order.

Sub Box2()
    Dim strResult As String
    strResult = InputBox(prompt:="Please enter amount", _
                                            Title:="Data Entry")
    ActiveCell.Value = strResult
End Sub

You really need to handle the Cancel button which always returns an empty String. Even if you click the OK button with no value this too will return an empty String.

In most cases, the following code should be added immediately after the InputBox function call:

If [Variable] = Empty Then Exit Sub

The above piece of code will terminate the procedure if the String variable is empty.

So the the previous example would look like:

Sub Box2()
    Dim strResult As String
    strResult = InputBox(prompt:="Please enter amount", _
                                            Title:="Data Entry")

If strResult = Empty Then Exit Sub 'Terminates here if empty
ActiveCell.Value = strResult
End Sub

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

Next Topic: Excel Input Box

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

VBA Keyword: InuptBox, ActiveCell, If...Then, Exit Sub