Excel VBA InputBox
Though most of the times you use the data which 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.
Syntax for VBA InputBox
Following is the syntax for VBA InputBox:
- Prompt – Message that gets displayed to the user. This is the only required argument, rest other arguments are optional.
- Title – It is the heading that appears on the dialog window after the successful execution of 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 X-axis.
- YPos – Position coordinate of a dialog box on 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
Sub InputBoxEX() InputBox "May I know Your Name?", "Personal Information", "Start Typing Here" End Sub
- Press F5 or run 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 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.).
Sub InputBoxEX() Dim Name As Variant End Sub
- Use InputBox to assign value to this variable called ‘Name’.
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 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.
4.7 (1,931 ratings)
- Now whatever value the user has typed in the dialog box, we want that to see in cell A1 of Excel sheet. Put the following statement for the same in VBE: Range(“A1”).Value = Name.
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 Run button or press F5 to run this code, you’ll get following dialog box. Write your name in the dialog box 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 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.
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 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 Application.InputBox.
Syntax for Application.InputBox is as follows:
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 Variant.
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:
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.
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.
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 providing the text as an input which is not acceptable by the system.
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.
- 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.
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 downloadable excel template. You can also go through our other suggested articles –