How to Input Dialog Box in Excel VBA (2 Easy Examples)

In this short tutorial, I will show you how to create an input dialog box through VBA in Excel. Fortunately, Excel has a built-in function named InputBox which allows us to display and insert data directly into a dialog box. So, here we will try to cover the InputBox function in detail and see some applications of it. So let’s get started.

Table of Contents hide

Download Practice Workbook

Overview of VBA InputBox Function

2 Useful Applications to Input Dialog Box in Excel Using VBA

1. Use of VBA InputBox Function to Input Dialog Box in Excel

2. Storing Data by Inserting Input Dialog Box Multiple Times

Things to Remember

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Input Dialog Box.xlsm

Overview of VBA InputBox Function

Description

The InputBox function in Excel VBA is a function that displays a dialog box for user input. It returns the information entered in the dialog box.

Generic Syntax

Application.InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

Argument Description

Name	Requirement	Data Type	Description
Prompt	Required	String	The text that will appear in the dialog window. It is possible for this to be a string, number, date, or Boolean type data (Before displaying, Microsoft Excel automatically coerces the value to a String). If the text is more than 255 characters then there will be no prompt and the Application’s function will return Error 2015.
Title	Optional	Variant	This is the text that will show up in the dialog box. This could be a string, number, date, or a Boolean value (Microsoft Excel automatically coerces the value to a String before it is displayed). If the length exceeds 255 characters, there will be no prompt and the function of the application immediately returns Error 2015.
Default	Optional	Variant	This specifies a value that will be shown in the dialog box‘s text field at startup. The text field is blank if this argument is left out. The value here might be a Range object.
Left	Optional	Variant	Gives the dialog box’s x location, expressed in points, with respect to the screen’s upper-left corner.
Top	Optional	Variant	Provides a y location, measured in points, for the dialog box in reference to the screen’s upper-left corner.
HelpFile	Optional	Variant	The title of this input box’s help file. A Help button will show up in the dialog box if the inputs HelpFile and HelpContextID are provided.
HelpContextID	Optional	Variant	This is the Helptopic’s context ID number in HelpFile.
Type	Optional	Variant	Specifies the kind of return data. In the absence of this option, the dialog window just displays text.

Return Value

Variant

2 Useful Applications to Input Dialog Box in Excel Using VBA

In this section, we will demonstrate 2 effective examples that will help you to understand the applications of the InputBox function. Let’s explore the examples one by one.

1. Use of VBA InputBox Function to Input Dialog Box in Excel

In the very first example, we will learn how we can use the VBA InputBox function to prompt users to insert their names in a dialog box. To do that, follow the steps below.

Steps:

Firstly, open the Visual Basic Editor window by clicking on Alt+F11. Then, click on Insert >> Module for creating a new module.

As a result, a new module named Module 1 will be created.
Now, we need to write a code that will enable us to display a dialog box for inserting the usernames of the users. To do that, copy the following code and paste it inside the module window.

Sub vba_inputbox_function()
InputBox "What's Your Name?", "Name of the User"
End Sub

Now, run the code by clicking the play icon or by pressing the F5 button.

As a result, we will see a dialog box titled “Name of the User” asking “What’s Your Name?”.

By running this code, we just take the name of the user but can’t store it inside our workbook. In the next example, we will see how we can use the VBA InputBox function to store data.

2. Storing Data by Inserting Input Dialog Box Multiple Times

In this example, we will not only prompt the user to insert their name in the dialog box, but we will store the data in the worksheet where we are working. To know more, follow the steps below.

Steps:

Firstly, like the 1st example, open the VBA editor and take a new module. Now in the module, write down the following code.

Sub store_InputBox_data()
Dim Nrow As Long
Nrow = WorksheetFunction.CountA(Range("B:B"))
Cells(Nrow + 3, 2).Select
ActiveCell = InputBox("What's Your Name?", "Enter Name")
End Sub

🗝️ How Does the Code Work?

Here, I have taken a subroutine named store_InputBox_data. Then, the number of filled cells in range B:B is calculated using the CountA function and stored in the Nrow variable. Then, we select a cell using Cells(Nrow+3,2) command where Nrow+3 is the row number and 2 is the column number of the B column. After that, using the InputBox function, we prompt the user to insert their names in a dialog box.

Now, we will run the code by pressing the F5 button or clicking the Play icon.

Consequently, a dialog box will appear asking for inserting the name of the user as in the figure below.

Now, write a name, and click OK. As a result, you will see that the name will be stored in cell B5.

Then if you run this code again, the next name will appear in cell B6 and this process will continue like this.

In this way, we can use the dialog box to store data very easily. If you want to run the code for multiple loops, you can use the following code.

Sub store_InputBox_data_loop()
Dim Nrow As Long
For i = 1 To 4
Nrow = WorksheetFunction.CountA(Range("B:B"))
Cells(Nrow + 3, 2).Select
ActiveCell = InputBox("What's Your Name?", "Enter Name")
Next i
End Sub

Here, you have to change the variable value i from 4 to the number of times you want the dialog box to take data.
Now, we will run the code by pressing the F5 button or clicking the Play icon.
Consequently, a dialog box will appear and it will ask for the user name 4 times in a row.

Things to Remember

In example 2, you can change the cell where you want to store the data by changing the number added to the variable Nrow.

Conclusion

That is the end of this article regarding how to input dialog box in Excel VBA. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exceldemy for more exciting articles on Excel.