VBA Input Box

What Is VBA Input Box In Excel?

VBA Input Box creates a pop-up window where the users are allowed to enter something based on the requirement. When we develop VBA projects at various stages, we may have to collect a few inputs from them to proceed further in the project. So, in those cases, we can design the input box.

Also, we can create an input box based on various criteria as well. For example, if we want the user to enter only numerical numbers, we can design the input box that way. Similarly, we can restrict the user to enter only date values.

Key Takeaways
  • VBA Input Box is a pop-up window and is used to get the inputs from the users.
  • The VBA Input Box types are Generic Input Box and Application.InputBox with data validation options.
  • Remember, there are various kinds of data validation techniques available with the Application.Input box.
  • Also, we can restrict the user to choose the input value only from the cell reference or range of cells by giving the type argument value as 8.
  • Variable can hold only the assigned data type value.

Syntax Of VBA Input Box

The syntax of VBA Input Box comes in two ways.

The first one is as follows:

INPUT(Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context])

Prompt: It is a mandatory argument. We can intimate the user about the kind of data they need to enter in the Input Box.

[Title]: We can give the title for the input box in this argument. It is an optional argument, and therefore, if we ignore this, we will get the application name as the title of the input box i.e., Microsoft Excel.

[Default]: In the input area of the input box, we can show the default value as per the requirement.

[XPos]: This is an optional argument. In this argument, we will enter a number that will position the coordinate of the input box on the X-axis.

[YPos]: This is an optional argument. In this argument, we will enter a number that will position the coordinate of the input box on the Y-axis.

[HelpFile]: In this argument, we will give the location of the help file that will be used with the input box. It is an optional argument however it becomes mandatory if we use the [Context] argument.

[Context]: This argument contains the context ID number associated with the help file. It is an optional argument however it becomes mandatory if we use the [HelpFile] argument.

The following image shows the typical input box in VBA.

VBA Input Box - Data collection

The syntax of the input box we have seen so far is the basic one with limited features. To get the advanced features of controlling the data validation in excel we need to use the “Application.InputBox” function.

VBA Input Box - Application

The syntax is as same as the generic input box as we have seen in the previous section. The additional feature we get when we use the Application.InputBox is the Type argument.

[Type]: This will allow us to do the data validation. Meanwhile, this is an optional argument.

For example, if we want the user to enter values only from the cell reference in excel, then we can put this validation in place. This is a lifesaver for all the developers because as a developer, we need not have to write multiple lines of code to do the data validation.

The following numerical values can be entered based on the requirement.

  • 0 – Only a formula can be entered.
  • 1 – Only a number can be entered.
  • 2 – Only a text string can be entered.
  • 4 – Only a logical value can be entered either TRUE or FALSE
  • 8 – Only a cell reference can be given as input.
  • 16 – Only an error can be entered such as #N/A.
  • 64 – An array of values can be entered.

Once the above data validations are put in place, users are still able to enter any of the data. However, the input box will do the data validation after the user clicks on OK and does not allow them to proceed further if the data entered is not as per the validation put in.

The VBA Input Box With A Variable

The best way to work with Input Box is through a variable. Now, let us see some examples using VBA input box.

Example #1 – The String Variable With An Input Box

Follow the steps listed here to create our first input box.

Step 1: First, select the Visual Basic Editor window by pressing the ALT + F11 shortcut keys.

Step 2: Next, go to the Insert tab and then, click on Module.

VBA Input - Example 1 - Step 2

Step 3: Now, this will insert the module and then, start the sub-procedure by giving a name to it.

VBA Input Box - Example 1 - Step 3

Step 4: Then, enter the input function (do not open brackets).

VBA Input Box - Example 1 - Step 4

Step 5: Next, for the prompt argument of the input box, we need to give a message to the user about what they need to enter. For example, assume we are collecting usernames; then, we can give a question such as What is your name?.

VBA Input Box - Example 1 - Step 5

Step 6: Similarly, for the Title argument of the input box, we need to give a title that is suitable for the context. Now, in this case, it will be User Name Collection.

VBA Input Box - Example 1 - Step 6

Step 7: Next, give the name default message to the user, like Enter your name here.

VBA Input Box - Example 1 - Step 7

Now, run the macro either by clicking on the Run button or pressing the shortcut key F5.

VBA Input Box - Example 1 - Step 7 - run

Meanwhile, when we run this button, we will get the following input box window.

VBA Input Box - Example 1 - Step 7 - box

As we can see, it has selected the default message. Thus, to enter, we need to press the backspace or delete button.

VBA Input Box - Example 1 - Step 7 - backspace

Now, enter the name in the input space.

Example 1 - Step 7 - XLMOJO

Next, click on OK and the input box will be disappeared.

However, to store the input received from the input box, we need to define a variable with the kind of data it can hold.

Since we are collecting names, we can assign the string data type like the following.

VBA Input Box - Example 1 - String

Now, enter this variable name along with an equal (=) sign then place the input box after the equal sign.

Example 1 - N-Input

But, we are getting an error with all the text or code in red color.

The reason for this is whenever we assign a value given by the input box, we need to enter the input box function with closed parenthesis.

VBA Input Box - Example 1 - N.jpg

Clearly, we can see a parenthesis has normal functions.

Now, let us show the variable value in the message box as shown in the below image.

Example 1 - Messagebox

Then, run the code, and we will get the below input box.

VBA Input Box - Example 1 - input box

Next, enter the name as required.

VBA Input Box - Example 1 - name

Now, click on OK and a message box will show the value entered in the input box.

VBA Input Box - Example 1 - output

Now, we have got the output to show somewhere. So, to store the value entered in the input box, use the following code.

Sub Input_Box_Example1()
Dim N As String
N = InputBox(“What is you name?”, “User Name Collection”, “Enter you name here”)
Range(“A1”).Value = N
End Sub

This will store the value entered in the input box in cell A1.

Example #2 – Number Variable With An Input Box

If we want to allow the user to enter only the numerical value, then we need to assign a data type that can hold the numerical data.

There are different numerical data types available. They are:

  • Byte – It can hold 0 to 255.
  • Integer – It can hold numbers from -32768 to 32768.
  • Long – It can hold numbers from -2,147,483,648 to 2,147,483,648
  • Single – It can hold numbers from -3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.

For example, if we are collecting age data from users then, we need to use the Byte data type because it can hold values from 0 to 255.

Example 2

Next, assign the value of the input box by designing the input box like the following.

Sub Input_Box_Example2()
Dim N As Byte
Age = InputBox(“What is your age?”, “Age Data”, “Enter you age here”)
Range(“A1”).Value = Age
End Sub

Now, when we run this code, we will get the following input box.

VBA Input Box - Example 2 - Run the code

Next, enter the age between 0 to 255.

Example 2 - age

Now, click on OK, and we will get the entered value in cell A1.

VBA Input Box - Example 2 - cell A1

Now, let us assume that we enter the age number, not within the range of 0 to 255.

Next, click on OK

Example 2 - out of range

Now, we will get the following error message.

Example 2 - Overflow

Clearly, we can see that the error says Overflow since the number we have entered is over the capacity of the data type that we have assigned.

Now, to fix these errors we need to use the data validation technique in the form of an Application.InputBox method.

Validation Of Input From User

We get more out of the VBA Input Box when we use it with the Application method. When we use this method, we will get the following syntax.

VBA Input Box - Application.jpg

The syntax looks the same as the generic input box, but we have an extra syntax called Type at the end.

By using the Type argument, we will restrict the user entry. Now, we will see all types of validation codes in the following section.

#1 – Only Numerical Value

Now, let us assume we need to allow the user to enter only numbers, then, we can set the type equal to 1 like the following code.

Sub Input_Box_Example2()
Dim Age As Byte
Age = InputBox(“What is your age?”, “Age Data”, “Enter you age here”)
Range(“A1”).Value = Age
End Sub

This code will not take any input values other than numbers. For example, when we run this, we have entered a value other than a number.

Age data

Next, click on OK to see the error message.

VBA Input Box - Validation 1 - number not valid

As we can see the error message says Number is not valid i.e., the entered value in the input box is not a number.

#2 – Only Logical Values (TRUE Or FALSE)

Similarly, if we want the users to enter only the logical value, we can set the type argument to 4.

Sub Input_Box_Example4()
Dim Question As String
Question = Application.InputBox(Prompt:=”Are you coming tomorrow?”, Title:=”Personal Data Collection”, Type:=4)
Range(“A1”).Value = Question
End Sub

Now, let us enter a value other than the logical value in the input box.

Validation 2 - Logical values

Next, click on OK, and we will get the following error.

Validation 2 - Error

Since we have entered a value other than TRUE or FALSE, we have the error message saying, Logical value is not valid. Hence, we need to enter only either TRUE or FALSE.

#3 – Only Cell References

If you want the users to select only cell reference as the input value, then, we need to set the type argument to 8.

Code:

Sub Input_Box_Example5()
Dim Price As Long
Price = Application.InputBox(Prompt:=”What is the SP Value?”, Title:=”Product Selling Price”, Type:=8)
Range(“A1”).Value = Price
End Sub

Remember, the above code will allow the user to select the input value only in the form of a cell reference. Therefore, if we enter an input value other than the cell reference, we will get the following error message.

VBA Input Box - cell references - SP
VBA Input Box - cell references - SP Error

It says the reference is not valid.

Now, we can either choose the cell, or we can directly enter the cell address.

Important Things To Note

  • Syntax of VBA Input Box has only one mandatory argument i.e., Prompt.
  • Remember, if we ignore the title of the input box, we will get the application name as the default title i.e., Microsoft Excel.
  • Also, when we assign the value of the input box by entering an equal sign, we need to use parenthesis.
  • Now, we can give input for any argument of the input box by entering the argument name followed by a colon and an equal sign. For example, to give input for the “Default” argument we can enter it like this “Default:=”

Frequently Asked Questions (FAQs)

1. What is the purpose of VBA input box in Excel?

The purpose of VBA Input Box in Excel is to design the input box such that, we can collect the value from the users based on the data required to proceed further in the coding based on the input given by the users in the input box.

2. How do I create a multiple-input box in VBA?

We can use generic INPUTBOX and APPLICATION.INPUTBOX to create multiple input boxes.

3. Why is VBA Input Box not working?

VBA input box requires brackets to be used when we assign a value to a variable if we do not enter argument values without the parenthesis VBA input box will not work.

4. What is the advantage of using the Application.InputBox method over the Input Box?

If we use the Application.InputBox method, we will get an additional feature like the TYPE argument to apply various data validation rules that make the user’s entry more or less error-proof.

Download Template

This article must be helpful to understand the VBA Input Box, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to VBA Input Box. Here we use input box function to create a pop-up window for users to enter the required values with examples and downloadable template. You may learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *