Welcome to Excel Avon
What is Input Box in Excel
The Input Box function prompts you to enter a value. If you click the OK or Enter button, the text box can be returned to the text box using the Input box function. And if you click the Cancel button, the input box function will return an empty string (“”). The InputBox function requires proper error handling. If you click on Cancel button without entering any value, it displays an error.
Input Box Syntax
Inputbox(prompt [, title] [, default] [, expose] [, ypos] [, helpfile, context])
Argument-
Prompt – The message the user sees is the only argument required in the input box and all other arguments are optional.
Title – Optional parameter. To display the string in the title bar of the box. And when the left side of the title is empty then the name of the application is placed in the title bar.
Default –This is an optional parameter. It takes the text as a default parameter in the textbox to display to the users.
XPos – Optional parameter. Position coordinate of a dialog box on the X-axis.
YPos – Optional parameter. Position the coordinates of the dialog box on the y-axis.
HelpFile – Optional parameter. A String expression that identifies the helpfile to be used to provide context-sensitive Help for the dialog box.
Context – Optional parameter. Help Numerical expression used to identify a reference number. This number is assigned by the help writer to the appropriate help topic. The helpfile must exist if we want to provide context.
How to create VBA Input Box
DOWNLOAD USED EXCEL FILE FROM HERE>>
So now we will create input box so now we will create input box but first go to developer tab of excel and then click in macro
and then you have to insert the module in VBE
Type command Input Box in the editor.
Sub VBA_InputBox() End Sub
Define the input value as a string
Sub VBA_InputBox() Dim InputValue As String End Sub
After typing the command input box will write prompt, title and default
Sub VBA_InputBox()
Dim InputValue As String
InputValue = VBA.InputBox("Please write input", "Input Title", "Some Text")
End Sub
Press F5 or Run the button to run this code
Now we will write debug.print input value and print from run button or with f5
Sub VBA_InputBox()
Dim InputValue As String
InputValue = VBA.InputBox("Please write input", "Input Title", "Some Text")
debug.print Inputval
End Sub
In the image you can see that only “sum text” was written here and this no. we have written and click ok.
No. written in the input box. , prints in the immediate window
How to Create Application Input Box
Displays a dialog box for user input. Returns the information entered in the dialog box. Application Input Box can be used for multi-purpose like string, Boolean, range and array.
Application Input Box Syntax
Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)
Arguments
Prompt – The message displayed in the dialog box. It can be a string, a number, a date, or a Boolean value.
Title – Optional parameter Title for the input box.
Default – The optional parameter specifies a value that will appear in the text box when it is displayed at the start of the dialog. This value can be a Range object.
Left – Optional parameter. Specifies the x position for the Points In dialog box.
Right – Optional parameter. Specifies the y position for the Points In dialog box.
HelpFile – If the Helpfile and HelpContextID arguments are present, a Help button will appear in the dialog box.
HelpContextId – Optional parameter. The Context ID number of help topic in helpline.
Type – If this argument is Blank, the dialog box returns text.
Type command for Application Input Box.
Sub VBA_Application_Input() End Sub
Now I will Define the Inputval as a variant
Sub VBA_Application_Input() Dim InputVal As Variant End Sub
After typing the command input box will write prompt, title and type Some will also ignore the argument.
Sub VBA_Application_Input()
Dim InputVal As Variant
Inputval = Application.Inputbox("Please make Input", "Input Title",,,,,,0)
Debug.Print Inputval
End Sub
Type 0 will only accept the Numerical value so debug. Print inputVal and run the code, and input window opens in which you will enter only numeric value
When you enter the numeric value, the numeric value will be printed in the Immediate window.
More way for Application Input Box
This time we will use the Application Input Box Syntax by changing it a bit.
Sub VBA_Application_Input() Dim InputVal As Variant InputVal = Application.InputBox(Prompt:="Please make input", Title:="Input Title", Type:=1) Debug.Print Inputval End Sub
Debug.Print Inputval and print then we will get this window
Writes a numeric value in the window, the value written in the window will be printed in the immediate window.
Type 2 Is Only for Text
Sub VBA_Application_Input() Dim InputVal As Variant InputVal = Application.InputBox(Prompt:="Please make input", Title:="Input Title", Type:=2) Debug.Print Inputval End Sub
Type 2 and click run button Input box open, Writes a non-numeric value to the window,
the value written in the window will be printed in the immediate window. If you enter numeric value in input box of type 2 then you will get invalid return.
Type 4 Is A logical value (True or False)
Sub VBA_Application_Input() Dim InputVal As Variant InputVal = Application.InputBox(Prompt:="Please make input", Title:="Input Title", Type:=4) Debug.Print Inputval End Sub
Type 4 and click run button Input box open, writes a True or false Also you can write 0 for false and 1 for true.
We had written 0 in the input box so in the Immediate window we will get return false
Type 8 Is A cell reference, as a Range object
But if we will use type 8 then we have to use set as in object variable. and write the address as it will reference the cell
Sub VBA_Application_Input() Dim InputVal As Variant Set InputVal = Application.InputBox(Prompt:="Please make input", Title:="Input Title", Type:=8) Debug.Print Inputval.Address End Sub
Type 8 and click on Run button Input box is open, here you can select the address or range of the cell
The range we selected got printed in the Immediate window
Type 16 Is An error value, such as #N/A
type 16 we will remove set and address and then click run button input box will open.
Sub VBA_Application_Input() Dim InputVal As Variant InputVal = Application.InputBox(Prompt:="Please make input", Title:="Input Title", Type:=16) Debug.Print Inputval End Sub
Here in the Input box we will write #n/a Immediate window will print error
As you can see error 2042 is printed in the immediate window
Type 64 is An array of values
Sub VBA_Application_Input() Dim InputVal As Variant InputVal = Application.InputBox(Prompt:="Please make input", Title:="Input Title", Type:=64) Debug.Print Inputval End Sub
So if you type any text in type 64 then you will get invalid
Invalid window
And if you want to print array then you have to use join function, The join function will be explained in the next post, how do you print type 64.
So, I hope you have understood How to use Input Box in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here
