In excel, we have multiple ways to get input. We can get input using function arguments, use excel cells and ranges as input areas and then retrieve values from there. You can also use userforms to get input from users. But excel provides one more way to get input from users. They are called InputBox.
An InputBox in VBA is a kind of predesigned user form. You can use an input box to get multiple types of input. But an input can get only one type of input at a time. Excel has two types of Inputboxes. One is simple InputBox and other is Application InputBox. Input box has multiple arguments to deal with different kinds of input. We will learn some of them in this article.
Simple VBA InputBox
This input box can be used directly in VBA because it is a function. This function prompts a user form to user to input value. The Syntax of InputBox function is:
|InputBox(prompt, [Title], [Default], [Xpos], [YPos], [HelpFile], [Context])|
prompt: It is the only required argument. It is a string expression displayed as the message in the dialog box. The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used. If prompt consists of more than one line, you can separate the lines by using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return-linefeed character combination ((Chr(13) & (Chr(10)) between each line.
[title]: It is optional. It is a string expression displayed in the title bar of the dialog box. If you omit the title, the application name is placed in the title bar. If you are using Excel, it will show Microsoft Excel.
[default]: It is optional too. It is a string expression displayed in the text box as the default response if no other input is provided. If you omit default, the text box is displayed empty.
[xpos]: Numeric expression that specifies, in twips, the horizontal distance of the left edge of the dialog box from the left edge of the screen. If xpos is omitted, the dialog box is horizontally centered.
[ypos]: Numeric expression that specifies, in twips, the vertical distance of the upper edge of the dialog box from the top of the screen. If ypos is omitted, the dialog box is vertically positioned approximately one-third of the way down the screen.
[helpfile]: String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If a help file is provided, context must also be provided.
[context]: Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, a help file must also be provided.
Enough of the theory. Lets have some examples to understand how to use this inputbox function.
Example 1: Get Input To Say Hi To the User
Let’s say I want my sub to greet the user. So I will ask for the name of the user and greet them as “Hello UserName”. For that I will use this code:
Sub InputBoxExample() user_name = InputBox("Please Enter Your Name.") MsgBox ("Hello " & user_name & "!") End Sub
When you run the above code in VBA, it will prompt a user form with heading “Please
Enter your Name.” with a text box below.
You can see that the window is named “Microsoft Excel” and the text box is empty. Because we haven’t used those arguments to set their value.
Example 2: Set The Title of Input Box as Greet User and set Default Value as User
In this example, we will set the title as greet user so that vba can not show Application Name and we will set the default value as User so that the textbox does show empty.
Sub InputBoxExample() user_name = InputBox("Please Enter Your Name.", "Greet User", "Uesr") MsgBox ("Hello " & user_name & "!") End Sub
When you run the above code, this is the output you will get.
The other arguments are not used much. They are basically used to set the location of userform on the excel edges. The last two arguments are used to provide contextual information to the user.
So yeah, now you can use inputbox to get input from the users in Excel using Inputbox function. I have explained Application.InputBox here. Please check this out too. It is more powerful than this inputbox function.
I hope I was explanatory enough and it was helpful to you. If you have any questions regarding this article or on any other VBA topic please let me know in the comments section below.