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

 excel_inputbox

When OK button is pressed

excel_inputbox_02

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.

excel_inputbox_03

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

2 thoughts on “Excel VBA Inputbox and Application.Inputbox

  1. Hello, you can try this

    Public Sub inputMethod()
    sinput = Application.InputBox(“Please input a percentage”, Type:=1)
    If sinput = False Then
    Exit Sub
    Else
    MsgBox (“Your input is ” & Format(sinput, “0.0%”))
    End If
    End Sub

Leave a Reply

Your email address will not be published.