On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Thursday, February 19, 2015

InputBox function in Excel VBA Code

InputBox Function

You can use the InputBox function in Excel VBA to prompt the user to enter a value. Place a command button on your worksheet and add the following code lines:

1. First, declare the variable myValue of type Variant.
Dim myValue As Variant

Note: we use a variable of type Variant here because a Variant variable can hold any type of value. This way the user can enter text, numbers, etc.

2. Add the following code line to show the input box.
myValue = InputBox("Give me some input")

InputBox Function in Excel VBA

3. Write the value of myValue to cell A1.
Range("A1").Value = myValue

Result when the user enters the value 5 and clicks the OK button.

InputBox Function Result

4. The InputBox function has more optional arguments. The following code line shows an input box with a title displayed in the title bar and has a default value. The default value will be used if no other input is provided.
myValue = InputBox("Give me some input", "Hi", 1)

InputBox Function in Excel VBA 

Result when the user only clicks the OK button.

InputBox Function Result

Note: Place your cursor on InputBox in the Visual Basic Editor and click F1 for help on the other optional arguments.