Excel VBA Inputbox and Application.Inputbox

This Excel tutorial explains difference between VBA Inputbox and  Application.Inputbox and how to use them.

You may also want to read:

Excel VBA Msgbox and handling of return value

Excel VBA use Inputbox to select options

Excel VBA Inputbox and Application.Inputbox

Inputbox is a pop up dialog box that displays a message and provides a text box for users to input free text. It is very similar to Msgbox except that Msgbox does not have a text box for user input.

There are two kinds of Inputbox: Inputbox and Application.Inputbox. Application.InputBox allows selective validation of the user’s input, and it can be used with Microsoft Excel objects, error values, and formulas. Note that Application.InputBox calls the InputBox method; InputBox with no object qualifier calls the InputBox function.

Syntax of Inputbox (Function)

InputBox(Prompt, [Title], [Default], [Left], [Top], [HelpFile], [HelpContextID])

Prompt	Required	The message to be displayed in the dialog box. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return–linefeed character combination (Chr(13) & Chr(10)) between each line.
Title	Optional	The title for the input box. If this argument is omitted, the application name is placed in the title bar.
Default	Optional	String expression displayed in the text box as the default response if no other input is provided. If you omit default, the text box is displayed empty.
Left	Optional	Specifies an x position for the dialog box in relation to the upper-left corner of the screen, in points.If omitted, the dialog box is horizontally centered.
Top	Optional	Specifies a y position for the dialog box in relation to the upper-left corner of the screen, in points. If omitted, the dialog box is vertically positioned approximately one-third of the way down the screen.
HelpFile	Optional	The name of the Help file for this input box. If the HelpFile and HelpContextID arguments are present, a Help button will appear in the dialog box.
HelpContextID	Optional	The context ID number of the Help topic in HelpFile.

Example of Inputbox (Function)

The below example demonstrates how to create an input box for users to input their names, and then use the name in MsgBox.

Note that if user press Cancel button, use sinput = “” to capture.

 Public Sub inputFunction()
    sinput = InputBox("Please input your name", "User Input", "default value")
    If sinput = "" Then
        Exit Sub
    Else
        MsgBox ("Your name is " & sinput)
    End If
End Sub

 

When OK button is pressed

Syntax of Application.Inputbox (Method)

Application.InputBox(Prompt, [Title], [Default], [Left], [Top], [HelpFile], [HelpContextID], [Type])

Prompt	Required	The message to be displayed in the dialog box. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return–linefeed character combination (Chr(13) & Chr(10)) between each line.
Title	Optional	The title for the input box. If this argument is omitted, the default title is “Input.”
Default	Optional	Specifies a value that will appear in the text box when the dialog box is initially displayed. If this argument is omitted, the text box is left empty. This value can be a Range object.
Left	Optional	Specifies an x position for the dialog box in relation to the upper-left corner of the screen, in points.If omitted, the dialog box is horizontally centered.
Top	Optional	Specifies a y position for the dialog box in relation to the upper-left corner of the screen, in points. If omitted, the dialog box is vertically positioned approximately one-third of the way down the screen.
HelpFile	Optional	The name of the Help file for this input box. If the HelpFile and HelpContextID arguments are present, a Help button will appear in the dialog box.
HelpContextID	Optional	The context ID number of the Help topic in HelpFile.
Type	Optional	Specifies the return data type. If this argument is omitted, the dialog box returns text. Value Description 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

As you can see, Application.Inputbox has one more argument “Type” which could perform validation.

Example of Application.Inputbox (Method)

The below example demonstrates how to create an input box for users to input their names, and then use the name in MsgBox.

You should use sinput = False to prevent error when users click on Cancel Button.

 Public Sub inputMethod()
    sinput = Application.InputBox("Please input your name", "User Input", "default value")
    If sinput = False Then
        Exit Sub
    Else
        MsgBox ("Your name is " & sinput)
    End If
End Sub

You may set the Range using Type:=8 in the last argument. In this case, you should use On Error to prevent error from pressing Cancel Button as well as setting value as Range.

Public Sub inputRanged()
On Error GoTo errHandler:
    Set rng = Application.InputBox("Please input data Range", "User Input", Type:=8)
    rng.Interior.ColorIndex = 19
errHandler: Exit Sub
End Sub

If you enter a value that is not correct (not a Range), an error message will pop up and then return you to the Inputbox. This is how the validation of data type is done with Application.Inputbox, such checking applies to other formats as well.

If you do not like the message from validation to pop up, turn it off with

Application.DisplayAlerts = False

Carriage Return

You can enter the text in two rows instead of one row by using Carriage Return Code vbCrLf

Example:

Application.InputBox("This is row 1" & vbCrLf & "This is row 2")

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff839468.aspx

https://msdn.microsoft.com/en-us/library/office/gg251811.aspx