VBA InputBox

VBA InputBox is an inbuilt function used to get a value from the user. This function has two major arguments: the heading for the input box and the question for the input box. The InputBox function can store only the data types input that its variable can hold.

Often in Excel, we use the data already in the Excel sheet. However, sometimes we need some input data from users as well. Especially in VBA, input from the user is required frequently.

Using InputBox, we can get the data from the user and use it for our purpose. An InputBox will ask the user to enter the value by displaying the InputBox.

• Prompt: This is nothing but the message to the user through an InputBox.
• Title (optional): What is the title of the InputBox?
• Default (optional): What is the default value of the InputBox? This value appears in the typing area of the InputBox.
• xpos, ypos (Optional) – Position of the input box on the screen (in pixels).
• helpfile, context (Optional) – For context-sensitive help (rarely used).

The first three parameters are usually good to use in Excel. One can ignore the other four optional parameters. To understand this syntax, look at the below screenshot.

Let us jump straight to practicality. Follow the below steps to create your first ever InputBox.

Step 1: Go to VBE (Visual Basic Editor), and insert a new module

Step 2: Double click on the inserted module and create a macro name.

Step 3: Start typing the word "InputBox" you will see related options.

Step 4: Select the InputBox and typea space, and you will see the syntax of the InputBox.

Step 5: Give the prompt "Please Enter Your Name."

Step 6: Type the title of the InputBox as "Personal Information."

Step 7: Type the default value as "Type here."

Step 8: We have completed it now. Run this code and see your first InputBox.

Store the Value of InputBox to Cells

Now, we will go through the process of storing values in cells. Follow the below steps.

Step 1: Declaring the variable as Variant.

Code:

Sub InputBox_Example()

Dim i As Variant

End Sub

Step 2: For this variable, assign the value through the InputBox.

Code:

Sub InputBox_Example()

Dim i As Variant

i = InputBox("Please Enter Your Name", "Personal Information", "Type Here")

End Sub

Note: Once the InputBox comes to the right of the equal sign, we need to enter the arguments or syntax in brackets like our regular formulas.

Step 3: Now, whatever the value typed in the input box, we need to store it in cell A1. So for this, write the code as Range ("A1").Value = i

Code:

Sub InputBox_Example()

Dim i As Variant

i = InputBox("Please Enter Your Name", "Personal Information", "Type Here")

Range("A1").Value = i

End Sub

We have completed it now. Let us run this code now by pressing the F5 key, or you can also run the code manually, as shown in the below screenshot.

As soon as you run this code, we will see the input box.

Type the name and click on "OK."

As soon as you type the name and click "OK," you will see the InputBox value in cell A1.

Note: We can store any value from the input box if the variable is defined properly. In the above example, we have defined the variable as a Variant that can hold all data types.

For example, now we have changed the variable type to Date.

Now, run the code and type other than the date.

Click on "OK" and see what the response is.

We got the error value as Type mismatch. Since we have declared the variable data type DATE, we cannot store anything other than DATE with an InputBox.

Now, enter the date and see what happens.

As soon as you type the date, click on "OK" and see what the response is.

Since we entered the correct value, we got the result in the cell.

Validation of Input from User

The best part of using the Input box is that  we can allow users to enter only specific values, i.e., allow the user to enter only text, only numbers, only logical values, etc.

To perform this task, we need to use the method Application.InputBox.

Let us look at the syntax of the Application.InputBox.

• Prompt: This is nothing but the message to the user through an InputBox.
• Title: What is the title of the InputBox?
• Default: What is the default value of the InputBox? This value appears in the typing area of the InputBox.
• Left: What should be the x position of the InputBox in the current window?
• Top: What should be the y position of the InputBox in the current window?

For starters, the InputBox declares a variable and assigns the value to a variable.

Now, assign a value to start the word Application.

After the word "Application," put a dot (.) and type "InputBox."

Then, select the input box and open the bracket.

As usual, enter Prompt, Title, and Default Value.

Now ignore left, top, help file, and help context ID by typing 5 commas (,).

Here, "Type" means what should be the input string. Below are the validations available.

So, accordingly, select your type. We have selected 1 as the parameter, i.e., only numbers.

Now, run the code and type of text value.

Click on "OK" and see what happens.

It says the number is not valid. So, we can enter only numbers in this InputBox.

• We need a variable to store the value given by the input box.
• If you are using InputBox without the Application method, you should be perfect about the variable data type.
• Use Variant data type, which can hold any data type and store it.