Input Boxes

Getting Input From Users in Excel VBA

InputBox() is used to get free-form information from a user.

The general format is:

InputBox (Prompt, Title, Default, Left, Top, Help_File, Context_id, Type)

Only the Prompt parameter is required, making a simple Input Box look like this:

InputBox(Prompt)

Parameter	Description
Prompt	REQUIRED. A message to display to the user, such as a question.
Title	OPTIONAL. A title for the input box that displays on the box.
Default	OPTIONAL. A default value that displays within in text entry area of an input box prior to the user entering any value.
Left	OPTIONAL. The position of the input box on the screen relative to the left-hand side of the screen.
Top	OPTIONAL. The position of the input box on the screen relative to the top of the screen.
Help_File	OPTIONAL. A reference to an external resource that may provide additional information related to the question being asked.
Context_id	OPTIONAL. Provide additional reference help indicating a context ID
Type	OPTIONAL. The type of value you want to be returned. Default is string/text.

The optional Type parameter allows you to specify the type of values you want to be entered in the input box. Using the Type parameter in a specific way may require prefacing the InputBox call with “Application.”

MyInformation = Application.InputBox("Give Me an Excel Formula", Type:=0)

Type	Description
0	Formula
1	Numeric Data
2	String (Default when not specified)
4	Boolean Value (True or False)
8	Reference to a cell within Excell
16	An Error Value
64	An Array

Example 1:

Private Sub CommandButton1_Click()
     Dim MyInformation As String
    
     MyInformation = InputBox("Tell Me a Secret")
    
     MsgBox MyInformation
 End Sub

The above code produces an input box that waits for me to enter in some text:

TellmeSecret

Typing text in and hitting okay results in the entered information being stored in the variable “MyInformation”

Example 2:

Private Sub CommandButton1_Click()
     Dim MyInformation As String
    
     MyInformation = InputBox("How are you doing today?", "A Question", "Tell me how you are doing")
    
     MsgBox MyInformation
 End Sub

The above code produces an input box with a title and a default value:

howdoing