VBA InputBox

Excel VBA InputBox

VBA InputBox is inbuilt function used to get a value from the user, this function has two major arguments in which one is the heading for the input box and another is the question for the input box, input box function can store only the data types input which it variable can hold.

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

Using the 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 your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA InputBox (wallstreetmojo.com)

Syntax

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

Excel VBA Input Box11

How to Create InputBox in VBA?

Ok, lets straight jump to practicality. Follow the below steps to create your first ever inputbox.

You can download this VBA InputBox Excel Template here – VBA InputBox Excel Template

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

VBA Input Box1

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

VBA Input Box2

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

Excel VBA Input Box4

Step 4: Select the inputbox and give space, and you will see the syntax of the inputbox.

VBA Input Box5

Step 5: Give the prompt as “Please Enter Your Name.”

VBA Input Box6

Step 6: Type the Title of the inputbox as “Personal Information.”

VBA Input Box7

Step 7: Type the default value as “Type here.”

VBA Input Box8

Step 8: We are done. Run this code and see your first input box.

VBA Input Box9
VBA Input Box10

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 input box 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

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.

VBA Input Box12

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

VBA Input Box13

Type the name and click on Ok.

VBA Input Box14

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

VBA Input Box15

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

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

VBA Input Box16

Now run the code and type other than the date.

VBA Input Box17

Click on ok and see what the response is.

Excel VBA Input Box18

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

Now enter the date and see what happens.

Excel VBA Input Box19

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

Excel VBA Input Box20

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, etc.

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

Let’s look at the syntax of the Application.InputBox.

VBA Input Box21
  • Prompt: This is nothing but the message to the user through an input box.
  • Title: What is the title of the input box?
  • Default: What is the default value of the input box? This value appears in the typing area of the input box.
  • Left: What should be the x position of the input box 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.

Validation of Input22

Now to assign value to start the word Application.

Validation of Input23

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

Validation of Input24

Select the input box and open the bracket.

Validation of Input25

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

Validation of Input26

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

Validation of Input27

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

Validation of Input28

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

Validation of Input29

Now run the code and type of text value.

Validation of Input30

Click on OK and see what happens.

Validation of Input31

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

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 kind of data type and store.

Recommended Articles

This 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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>