How to use Input Box in Excel

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

input_box-Vba

and then you have to insert the module in VBE

message-box-in-excel (2)

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

input_box-Vba1

Press F5 or Run the button to run this code

input_box-Vba2

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.

input_box-Vba3

No. written in the input box. , prints in the immediate window

input_box-Vba3.png

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.

TypeIf 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

input_box-Vba45

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

Inputvalue.print

Writes a numeric value in the window, the value written in the window will be printed in the immediate window.

Inputvalue.print.

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,

Inputvalue.print..png

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.

Inputvalue.print..3

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.

Inputvalue.print..3 (2)

We had written 0 in the input box so in the Immediate window we will get return false

Inputvalue.print..35

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

Inputvalue.print..36

The range we selected got printed in the Immediate window

Inputbox-175724

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 errorinput_box-Vba5

As you can see error 2042 is printed in the immediate window

input_box-Vba6

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

input_box-Vba8

Invalid window

input_box-Vba9

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

Learn More Topic in VBA here

You can also see well-explained video here

Leave a Reply