VBA InputBox

Excel VBA InputBox

Most of the time, you use the data that is already with you; sometimes, you come up with a situation where you want the user to input the data like Name, Age, etc., kind of personal information. These kinds of input information are needed sometimes when we are conducting a survey and need to review the unbiased opinion of people.

Using Excel VBA’s InputBox, we can get the input data from the user. As the name suggests, InputBox works as a pop-up box which asks the user to feed certain information.

The syntax for VBA InputBox

Following is the syntax for VBA InputBox:

Where,

• Prompt – Message that gets displayed to the user. This is the only required argument; the rest other arguments are optional.
• Title – The heading appears on the dialog window after the successful execution of the InputBox statement. If not given, by default system prints ‘Microsoft Excel’ as a title.
• Default – It is the default value that comes on the dialog box. We can keep it null as well. No default value set for this argument.
• XPos – Position coordinate of a dialog box on the X-axis.
• YPos – Position coordinate of a dialog box on the Y-axis.
• HelpFile – Location of the help file that should be used. This argument becomes mandatory to set when the ‘Context’ argument is passed.
• Context – Represents Help ContextId for the HelpFile used. Mandatory to use when ‘HelpFile’ argument is passed.
• Out of all these arguments, only the first three are enough to successfully create an InputBox.

Creating InputBox in Excel VBA

Below are the different steps to create InputBox in Excel using VBA Code.

• Open VBE (Visual Basic Editor by pressing Alt + F11 simultaneously in an Excel file and click on Insert and add a new Module in VBE.

• Create a macro in this module named ‘Module1’. Assign a name to the macro.

• Type command InputBox in the editor.

Give the following inputs to the InputBox statement:

• Prompt: “May I Know Your Full Name?”
• Title: “Personal Information”
• Default: “Start Typing Here

Code:

Sub InputBoxEX()

InputBox "May I know Your Name?", "Personal Information", "Start Typing Here"

End Sub

• Press F5 or run the button to run this code.

How to Store the Output of InputBox to Cells?

You have created the InputBox to get the input from the user. However, where the output will get stored? We haven’t mentioned any location where the output can be stored.

Let’s store the output we get from InputBox to excel cells by following the below steps in VBA:

• Declare a new variable ‘Name’ with type as ‘Variant’. This type of variable can take any value (numeric/string/logical etc.).

Code:

Sub InputBoxEX()

Dim Name As Variant

End Sub

• Use InputBox to assign a value to this variable called ‘Name’.

Code:

Sub InputBoxEX()

Dim Name As Variant

Name = InputBox("May I know Your Name?", "Personal Information", "Start Typing Here")

End Sub

If you could have noticed the parenthesis after the InputBox statement, those are needed because we are using this statement for a variable ‘Name’.  As soon as it becomes a value to be given for a variable,  it has to be mentioned in a proper format with parenthesis.

• Now whatever value the user has typed in the dialog box, we want that to see in cell A1 of the Excel sheet. Put the following statement for the same in VBE: Range(“A1”).Value = Name.

Code:

Sub InputBoxEX()

Dim Name As Variant

Name = InputBox("May I know Your Name?", "Personal Information", "Start Typing Here")

Range("A1").Value = Name

End Sub

This is it; now, let’s run this code and see how it works.

• Click on the Run button or press F5 to run this code; you’ll get the following dialog box. Write your name in the dialog box that popped up with the name “Personal Information” and hit OK to see where the output gets printed.

• As soon as you input the value and click on OK, you can see the value inputted in cell A1. See the screenshot below.

Any value can be stored using InputBox if the variable is properly defined. In this case, you have defined the variable ‘Name’ as ‘Variant’. The variant data type can take any data value, as I said earlier.

See the example below:

I am giving a number as an argument to dialog box when it pops up. See as below:

Click on OK, see the output below:

The value in cell A1 is changed to 2019.

Now let’s change the variable type to Date.

Code:

Sub InputBoxEX()

Dim Name As Date

Name = InputBox("May I know Your Name?", "Personal Information", "Start Typing Here")

Range("A1").Value = Name

End Sub

Run the code and try to input the value other than the date. I will enter my name itself and click OK.

• After clicking OK, a run time error message saying, ‘Type mismatch’.

It occurred because the type of variable name is Date now, and I have given an input argument other than date value (a string name). Due to which this code doesn’t execute and throws an error.

Validation of User Input

What if I tell you that the user input can be restricted? Yes, It’s true! You can restrict the user input to Characters, Numbers or Logical, etc.

To restrict the user input, you can use the Application.InputBox.

The syntax for Application.InputBox is as follows:

Where,

Prompt – Message that gets popped up for the user.

Title – Heading of the dialog box.

Default – Default value that pops up on typing area under dialog box.

Type – The type of input.

These are the only important arguments which are enough to run this statement.

Let’s start this through example.

• Declare a variable Name as a Variant.

Code:

Sub InputBoxEX()

Dim Name As Variant

End Sub

Assign Application.InputBox to the variable called Name with the same arguments as those you have used InputBox. i.e. Prompt, Title and Default. See the code below:

Code:

Sub InputBoxEX()

Dim Name As Variant

Name=Application.InputBox("May I know Your Name?","Personal Information","Start Typing Here")

End Sub

Now, put comma 5 times to ignore the Left, Top, HelpFile and HelpContextID. After 5 commas,  you can specify the input type.

Code:

Sub InputBoxEX()

Dim Name As Variant

Name = Application.InputBox("May I know Your Name?","Personal Information","Start Typing Here",,,,,

End Sub

Type of the input string have below mentioned validations:

• Let’s choose 1 as a Type in our statement. It means only Numbers/Numeric Values are acceptable in the dialog box which pops up.

Code:

Sub InputBoxEX()

Dim Name As Variant

Name = Application.InputBox("May I know Your Name?","Personal Information","Start Typing Here",,,,,1)

End Sub

• Run the code manually or using the F5 key and give name as an input in the typing area as shown below. Then click OK and see the Output.

It says the number is not valid. That seems logical because we have set the variable input type as the number and provide the text as an input that is not acceptable.

In this way, you can restrict the user to enter only the values which you want to see through InputBox.

Things to Remember

• InputBox accepts up to 255 arguments and can display only 254. So be cautious about the max length a user can enter.
• The application method can be used to set the input data type. However, if it is not used, then you need to be more specific about the input data type.
• A variant data type is recommended to choose as it can hold any of Numeric/Text/Logical etc. B=Values.

Recommended Articles

This has been a guide to VBA InputBox. Here we discussed how to create InputBox in Excel using VBA code along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. VBA Get Cell Value
2. VBA VLOOKUP
3. VBA Input
4. VBA RGB