Input Box is one of the most frequently used functions in VBA Macro. The dialogue box that appears asks for input from the user and returns a constant/code/number/text. For example, if you want to create a log-in form in VBA, you will require an input box function. Let’s learn how to create an input box in VBA.
InputBox Function
Input Box is a dialogue box that helps users to take value, and do later computations according to the entered value. The input box is similar to the message box, but the message box is used to display the data while the input box is used to enter the data. By default, a message box shows only the OK button in its dialogue box but the input box shows both the OK and Cancel buttons in its dialogue box. You can choose the return type of the data to be entered. The automated data analysis includes entering and displaying the data, which can be achieved if we are using the input box and message box simultaneously.
Syntax: InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile, context])
Temporary and Permanent Arguments in Input Box
Arguments of the Input box, help customize your input box by changing the title, fixing the position, adding help buttons, etc. In total, eight(8) arguments can be passed in the input box. Out of which one(1) is the permanent argument, and leftover seven(7) are the temporary arguments.
Here is the list of all arguments used by the Input box.
| Data Type |
Title |
| |
Default |
| |
X-Position |
| |
Y-Position |
| |
Helper |
| |
Context ID |
| |
Prompt |
Parameter Description
Prompt :
This parameter is a string that serves as the message displayed within the dialog box. The message can contain up to approximately 1024 characters. If the message spans multiple lines, you can separate the lines using either the carriage return character or the linefeed character.
Title:
This parameter is also a string expression that appears in the title bar of teh dialog box. If you leave this parameter empty, the title bar will display the name of the application.
Default:
Here, you can provide a default text that will be pre filled in the text box of the dilaog box. Users have the option to accept or modify this default text.
XPos:
This numeric parameter represents the X -axis position of the dialog box. It determines how far the dialog box should be positioned from the left said of teh screen horizontally. If left blank, the input box will be centered horizontally.
YPos:
Similar to XPOs, YPOs determine the Y-axis position of the dialog box. It specifies the distance of the dialog box from the top of the screen vertically. If left blank, the input box will be centered vertically.
Helpfile:
If you want to provide context-sensitive help for the dialog box, you can use this parameter. Provide a string expression that identifies the Helpfile to be used .
Context:
This numeric expression is used to indicate the Help context number assigned by the help author to the relevant help topic. If you provide this parameter, you must also provide the Helpfile parameter.
VBA is smart enough to give you a list of temporary and permanent arguments. Following are the steps:
Step 1: Create a Procedure
Open your VBA editor. Create a procedure named geeks(). Use InputBox, function, as soon as you start the parenthesis for the function, a list automatically appears on your screen.
Create procedure and use inputbox function
Step 2: List Generated will Give Arguments
The list tells the order of arguments. The argument written in bold and without a square[ ] parenthesis are permanent arguments, while the arguments written without a square[ ] parenthesis are temporary arguments.
Arguments given
Arguments in Input Box
Now, we are going to understand every argument in detail, with examples.
Argument 1: Prompt
Prompt is the definition for the input box, according to which understanding the user enters the data. Prompt is a required argument. If this argument is not provided, then the VBA might show an error. The prompt returns a string. For example, display the “Enter Your Name” prompt in the input box.
Following are the steps
Step 1: Declare a Variable
Declare a variable to store the input data in the input box. For example, name. Use the InputBox function. Write InputBox(“Enter Your Name”) and store this in the variable name.
Declare a variable
Step 2: Run Macro
Run your macro. You can see the input box appears on the screen, with the prompt “Enter Your Name”.
Run macro
Argument 2: Title
The second argument provided to the input box is the title. The title is the heading of an input box. It is a temporary argument. The default data type of a title is variant. If a custom title is not mentioned, then the default title is “Microsoft Excel”. For example, display “geeks for geeks” as the title of your input box.
Following are the steps
Step 1: Open VBA Editor and Create Sub Procedure
Open your VBA editor. Create a sub-procedure called geeks(). Use the InputBox function. The argument 1 of the input box function is the prompt text; it is the same as done in previous examples. The second argument is the title of your input box i.e. “geeks for geeks”. Store the input box function in the variable Name.
Create sub procedure
Step 2: Run Macro
Run your macro. An input box is shown on the screen. You can see the title of the input box is “geeks for geeks”.
Run Macro
Argument 3: Default
The third argument provided to the input box is the “default”. The “default” is the initial text box value. There can be situations when a user might not enter any data. At that time, the variable used to store the data remains empty. This may cause errors in the code, to remove this breakpoint we can add a default value in the custom text box. It is a temporary argument. The default data type of “default:” is variant. If a custom “default” is not mentioned, then the text box has no value. For example, display “geeks” as the default value of your text box.
Argument 3: Default
Following are the steps
Step 1: Open VBA Editor and Create Sub Procedure
Open your VBA editor. Create a sub-procedure name geeks(). The first two arguments of the input box are the same as the above example. The third argument is the custom text box value i.e., “geeks”. Store the input box function in the variable Name.
Open VBA and create sub procedure
Step 2: Run Macro and Check Default Value
Run your macro. An input box is shown on the screen. You can see the default value of the text box is “geeks”.
run macro and check default value
Argument 4 and Argument 5: XPos and YPos
The fourth argument is X- Position(Xpos) and the fifth argument is Y-Position(YPos). Both are the positional arguments provided to set the location of your input box with respect to the home screen of excel. They are temporary arguments. The default data type is variant. For example, XPos as 2000 and YPos as 10000 in the input box.
Following are the steps
Step 1: Open VBA Editor and Create Sub Procedure
Open your VBA editor. Create a sub-procedure name geeks(). The first three arguments of the input box are the same as the above example. The fourth argument is a numeric value of 2000, and the fifth argument is 10000. Store the input box function in the variable Name.
Open VBA Editor and create sub procedure
Step 2: Run Macro
Run your macro. You can analyze the position of the input box, it has gone to the end of the worksheet. The X and Y positions can be changed accordingly.
Run Macro
Argument 6 and Argument 76: HelpFile and HelpContext
A HelpFile is provided by the author for resolving errors or to guide how to fill the input box. A HelpFile is called when a user clicks on the help button in the input box. Whenever you are providing a HelpFile, you also need to mention the context ID, which specifies the number of the help topic in the HelpFile. If no HelpFile is specified, then a visual basic file is displayed. You will rarely use, HelpFile and HelpContextId in the input box.
Both HelpFile and Context are optional arguments and have data types as variants. The file used by Context is the .hlp file. You might not get a suitable example on the help file and context as they are generally provided by the author or by the VBA community itself.
Argument 8: Data type
The eighth argument of the input box function is a data type. It is a temporary argument and is not present in the default input box function. Yes, here is the catch, the VBA input box function does not provide a type argument, but an excel specific souped-up provides Application.InputBox function, which has an eighth(8th) data type called type. The type argument is very useful to limit the user’s access to the data. The data type is mentioned in the form of a number. For example, if you want to enter your age in the input box, you will never require text, so this could be limited by type argument.
Type not present
Type present
Here is the list provided by Microsoft to refer to the data type numeric value:
| 0 |
Formula |
| 1 |
Number |
| 2 |
Text |
| 4 |
Boolean |
| 8 |
Range Object |
| 16 |
error(#N/A) |
| 64 |
Array |
How to Use Input Box in a VBA Code
Follow the below steps to use Input Box
Step 1: Open VBA Editor and Create Sub-procedure >Use Application.InputBox() function
Open your VBA editor. Create a sub-procedure name geeks(). Use Application.InputBox() function to access the type argument. Store the result in the variable age. Print the age variable in the immediate window. As you have passed data type as 1, you can only enter numbers in the input box. Run your Macro.
Open VBA Editor and create sub procedure
Step 2: Error Message Displayed
Now, if you try to enter a text in the input box, then it will show an error.
Error
Step 3: Dilog Box Popped Up
A new dialogue box appears, named Microsoft Excel, which specifies you cannot enter a text in the input box.
Dilaog box showing message
Step 4: Enter Number and Error Removed
Now, if you enter a number, for example, 19, click Ok. It won’t show any error.
Enter number
Step 5: Number is Printed
The number 19 is printed in the immediate window.
Number printed
Skipping the Arguments
There can be a probability that you don’t want all the arguments to be added to your input box function. For example, you generally do not require help files and help context arguments, but you want to add a data type argument in the input box. This issue can be solved by skipping the arguments. There are two most used ways to skip arguments in a function.
Method 1: Use (comma) to skip Arguments
The comma (,) is used to skip the arguments in the function. For example, you want to add the prompt “Enter your name” and default value “Arushi”, but want to skip the temporary argument title.
Following are the steps
Step 1: Open VBA Editor
Open your VBA editor. Write Application.InputBox(“Enter your name”, , “Arushi”), an empty comma(,) will skip the second argument title.
open VBA Editor
Step 2: Run Macro
Run your macro. You can see that the default title appears in the input box i.e., “Input”. You have successfully skipped the title argument.
Run Macro
Method 2: Specifying the argument name
You can mention the name-specific arguments. For example, you want to add the prompt “Enter your name” and default value “Arushi”, but want to skip the temporary argument title.
Example
Step 1: Open VBA Editor and Write Application.InputBox()
Open your VBA editor. Write Application.InputBox(Prompt := “Enter your name”, Default := “Arushi”), using argument_name := specifies the argument to be added in the input box.
Step 2: Run Macro
Run your macro. The prompt “Enter your name”, with the default “Arushi”, is displayed in the input box.
FAQs on InputBox Function in Excel VBA
Q1: What is the InputBox Function in Excel VBA?
Answer:
The InputBox Function in Excel VBA is used to display a dialog box that prompts the user for input. It allows users to enter text or values, which can then be used in your VBA code.
Q2: How to use the InputBox Function in VBA?
Answer:
You can use the InputBox Function by providing a prompt message as an argument. The Function returns the value entered by the user. For example:
Dim userInput As String
UserInput = InputBox(“Please enter your name”)
Q3: Can I customize the title of the InputBox Dialog box?
Answer:
Yes, you can customize the title of the dialog box by providing a second argument to the function. For example:
Dim userInput As String
userInput = InputBox (“Please enter your name :”, “Name Input”)
Q4: Is the InputBox Function Case-Sensitive?
Answer:
Yes, by default, the InputBox Function is Case-sensitive . You’ll need to handle case-insensitive comparison in your code if necessary.
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...