Excel VBA InputBox

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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA InputBox (wallstreetmojo.com)

Syntax

  • 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.

These three parameters are good enough in Excel. Ignore the other four optional parameters. To understand this syntax, look at the below screenshot.

How to Create InputBox in VBA?

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 give 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.

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

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 inputbox.

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 inputbox 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

You know what? 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 starting, 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.

Things to Remember

  • 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.

This article has been a guide to VBA InputBox Function. Here, we learn how to create a VBA InputBox and process the stored values, along with some practical examples and a downloadable Excel template. Below are some useful Excel articles related to VBA: –

  • UBOUND VBAUBOUND VBAUBOUND, also known as Upper Bound, is a VBA function that is used in conjunction with its opposite function, LBOUND, also known as Lower Bound. This function is used to determine the length of an array in a code, and as the name suggests, UBOUND is used to define the array’s upper limit.read moreVBA MsgBox ExamplesVBA MsgBox ExamplesVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read moreWhat is Combo Box in Excel?What Is Combo Box In Excel?Combo Box in Excel is a type of data validation tool that can create a dropdown list for the user to select from the pre-determined list. It is a form control which is available in the insert tab of the developer’s tab.read moreVBA ComboBoxVBA ComboBoxIn VBA, a ComboBox is a user form feature that restricts the user to the response type that is intended. As a result, the data is in a logical order. read moreVBA Send Email from ExcelVBA Send Email From ExcelWe can use VBA to automate our mailing feature in Excel to send emails to multiple users at once. To use Outlook features, we must first enable outlook scripting in VBA, and then use the application method.read more