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”

pancakes.png

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

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

howdoingwell.png

 

Excel VBA Resources

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: