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

Get FREE Advanced Excel Exercises with Solutions!

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

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


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.

Use of VBA InputBox Function to Input Dialog Box in Excel

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

Use of VBA InputBox Function to Input Dialog Box in Excel

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

Use of VBA InputBox Function to Input Dialog Box in Excel

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

excel vba dialog box input

🗝️ 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.

Storing Data by Using VBA InputBox Function

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

excel vba dialog box input

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

excel vba dialog box input

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

excel vba dialog box input

  • 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

excel vba dialog box input

  • 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 Open the Insert Function 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.


Related Articles


<< Go Back to Excel Dialog Boxes | Excel PartsLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo