Aniruddah Alam
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.
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
- 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
Input Dialog Box in Excel Using VBA: 2 Useful Applications
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 to create 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.
Read More: How to Create a Dialog Box in Excel
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.
Read More: How to Close Dialog Box in Excel
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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. Do let us know if you have any further queries.
