5 Examples to use Excel/VBA InputBox
InputBox:is a function in VBA i.e. used to display a textbox field/prompt to the user to take input. It Returns a string as user clicks the “Ok” button or press enter
A little about InputBox in VBA
The InputBox:
- is a function in VBA
- used to display a textbox field/prompt to the user to take input
- Returns a string as the user clicks the “Ok” button or presses enter
- Also has a “Cancel” button that returns nothing
- The title can be set for the InputBox for making it more descriptive for the user
- Default text can also be displayed
- See examples below for various InputBoxes
Syntax of InputBox with all arguments
InputBox(prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfile, context ])
The examples below use most of the arguments – one by one.
A simple InputBox in VBA
We simply displayed the input box by using InputBox function without providing any title or default value.
Code:
Sub inputbox_ex()
input_string = InputBox("Enter Your Name?")
End Sub
As we run this code:
Display InputBox string in a message box
We will assign the entered value to a String variable.
As you click OK or press Enter after writing some text in the input box, it will be displayed in the message box:
Sub inputbox_ex()
Dim input_string
input_string = InputBox("Is VBA cool?")
MsgBox input_string
End Sub
Output:
Prompt:
Message box:
An example of InputBox with Title
As mentioned earlier, you may also set the input box title by using the optional “title” argument. This will replace “Microsoft Excel” by the given text in the title bar of the dialog box.
See an example below:
Sub inputbox_ex()
Dim title, input_string
title = "Enter Any Programming Language"
input_string = InputBox("Java, Python, C++, C - None?", title)
MsgBox input_string
End Sub
Output:
Setting the default text for InputBox
This may help further, along with the title bar, what type of information the user is supposed to enter, mostly used input value, Or if the user accidentally clicked OK without entering any information – to avoid crashing the program due to error.
The VBA program below shows sample default text:
Sub inputbox_ex()
Dim title, input_string, default_val
title = "Cool Coding in?"
default_val = "VBA"
input_string = InputBox("Java, Python, C++, C, VBA - None?", title, default_val)
MsgBox input_string
End Sub
Output:
Setting horizontal and vertical distances from the screen
- The next two arguments (after default) are xpos and ypos in the InputBox dialog.
- As we have omitted both in all the above examples, the dialog appeared in the center of the screen.
- If you want to display it somewhere else, you can do it by xpos (horizontal distance) and ypos (vertical distance) arguments.
- The following example shows its usage along with the above used arguments:
Sub inputbox_ex()
Dim title, input_string, default_val
Dim x_dis, y_dis
title = "Cool Coding in?"
default_val = "VBA"
x_dis = 540
y_dis = 625
input_string = InputBox("Java, Python, C++, C, VBA - None?", title, default_val, x_dis, y_dis)
End Sub
Result:
