VBA InputBox

VBA InputBox (Table of Contents)

• How to Create VBA InputBox?
• How to Store the Value of InputBox to Cells?
• Validation of Input from User

InputBox in Excel VBA

Often in excel, we use the data which is already there in the excel sheet. Sometimes we need some kind of input data from users as well. Especially in VBA input from the user is required often times.

By using the InputBox in VBA Excel 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 in VBA Excel.

Formula of InputBox in VBA

Let’s look at the syntax of INPUTBOX in VBA Excel.

• Prompt: This is nothing but the message to the user through an inputbox.
• Title: What is the title of the VBA 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 other 4 optional parameters. To understand this syntax look at the below screenshot of the VBA inputbox.

How to Create InputBox in VBA Excel?

Ok, lets straight jump to practicality. Follow the below steps to create your first ever Excel VBA 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 as “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 are done. Run this code and see your first inputbox.

How to Store the Value of InputBox to Cells?

We have learned how to create an inputbox in VBA Excel. Now we will go through the process of storing these values in cells. Follow the below steps.

Step 1: Declare 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 Excel VBA 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 inputbox 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

Ok, we are done. Let’s 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 on OK, you will see the inputbox value in cell A1. Take a look at the below Screenshot.

Note: We can store any value from the inputbox if the variable defined properly. In the above example, I have defined the variable as Variant which can hold all types of data.

For example, now I 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 as DATE, we cannot store anything other than DATE with a Excel VBA inputbox.

Now enter the date and see what happens.

As soon as you type date and then Click on OK and see what the response is.

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

Validation of Input from User

You know what we can actually allow users to enter only specific value i.e. allow the user to enter only text, only number, only logical values and etc.

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

Let’s look at the syntax of 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?

To start this inputbox declare variable and assign the value to a variable.

Now to assign value to start the word Application.

After the word Application put a dot (.) and type Inputbox.

Select inputbox and open the bracket.

As usual enter Prompt, Title, and Default value.

Now ignore left, top, help file, 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. I have selected 1 as the parameter i.e. only numbers.

Now run the code and type 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 About Excel VBA InputBox

• We need a variable to store the value given by the inputbox.
• If you are using InputBox in VBA without Application method then you should be perfect about variable data type.
• Use Variant data type which can hold any kind of data type and stores.

Recommended Articles

This has been a Guide to VBA InputBox. Here we learn how to create a VBA InputBox and process the stored values along with some practical examples. Below are some useful excel articles related to VBA –

• VBA Dictionary
• UBOUND VBA
• VBA Cells Reference
• Range in VBA Examples
• List Box in VBA
• VBA MsgBox Examples
• Combo Box in VBA
• VBA Training Bundle