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.
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.
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.
Related Articles:
Getting Started With Excel VBA UserForms| I will explain how to create a form in excel, how to use VBA toolbox, how to handle user inputs and finally how to store the user inputs. We will go through these topics using one example and step by step guide.
VBA variables in Excel| VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.
Excel VBA Variable Scope| In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.
ByRef and ByVal Arguments | When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.
Delete sheets without confirmation prompts using VBA in Microsoft Excel | Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.
Add And Save New Workbook Using VBA In Microsoft Excel 2016| In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc
Display A Message On The Excel VBA Status Bar| The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering.
Turn Off Warning Messages Using VBA In Microsoft Excel 2016| This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
